Saturday, February 9, 2019

How to Calculate Cumulative Sum using EARLIER Function in Power BI

How to Calculate Accumulative Total using EARLIER Function in Power BI
EARLIER is a DAX function acts exclusively on row context,  and its purpose is to create a reference to a column value on an outer loop of the evaluation of the expression.  
It is commonly used in Calculated Columns during nested row by row iterations.
Syntax :
EARLIER(<column>, <number>)  
Parameters :
column : A column or expression that resolves to a column.
num :  A positive number (optional) to the outer evaluation pass. The next evaluation level out is represented by 1; two levels out is represented by 2 and so on. When omitted default value is 1.

Scenario :
Suppose we have the data in a Power BI Report as follows..
Note :
In the above table, the Measure [NetSales] is calculated using the below DAX Expression.
TotalSales = SUMX(FactSales,FactSales[Units_Sold]*RELATED(DimProducts[Unit_Price]))

Now we can calculate the Total Cumulative Sum, and Cumulative Sum by Month using the EARLIER Function as follows..
Cumulative Sum by Month :
MonthCumSales = CALCULATE(FactSales[TotalSales],
FILTER(FactSales,FactSales[Order_Date]<=EARLIER(FactSales[Order_Date])),
FactSales[MonthN]<=EARLIER(FactSales[MonthN]))

Total Cumulative Sum :
TotalCumSales = CALCULATE(FactSales[TotalSales], 
FILTER(FactSales,FactSales[Order_Date]<=EARLIER(FactSales[Order_Date])))
Result :
How Earlier Function Works :
The EARLIER Function, takes the first date value '1/5/2014' and compare it with other date values , checks for the specified condition (<= or >=) in the Filtered row Context, if the condition satisfies that value will be includes in to the Sum calculation. This iteration will repeats till the last date value. 

-------------------------------------------------------------------------------------------------------- 
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.