Running Total for a value in DAX measure

[miiihiir]

Hello DAX People,
So basically I need to create a Running Total, I have created a measure which is calculating active EMIs(SIP), I have visualized it on the bargraph as well (screenshot attatched).

Calculations for Measure:

Active SIP =

[Total SIP Started] - [Total SIP Stopped/ Completed]

Total SIP Started =

CALCULATE(SUM('T_ORDER (For SIP)'[order_amount]), FILTER('T_ORDER (For SIP)', 'T_ORDER (For SIP)'[order_type] = "SIP" && ('T_ORDER (For SIP)'[order_status] = "Executed" || 'T_ORDER (For SIP)'[order_status] = "Running" || 'T_ORDER (For SIP)'[order_status] = "Stopped" || 'T_ORDER (For SIP)'[order_status] = "Completed")))

Total SIP Stopped/ Completed =

CALCULATE(SUM('T_ORDER (For SIP)'[order_amount]), FILTER('T_ORDER (For SIP)', 'T_ORDER (For SIP)'[order_type] = "SIP" && ('T_ORDER (For SIP)'[order_status] = "Stopped" || 'T_ORDER (For SIP)'[order_status] = "Completed")))

So,

Here one more thing we need to add in this bar graph, is an another bar showing running total of EMIs over months. 
I tried creating the same using this logic:

Cumulative =

CALCULATE([Running SIP], FILTER('T_ORDER (For SIP)', 'T_ORDER (For SIP)'[order_date] <= MAX('T_ORDER (For SIP)'[order_date])))

Also used this measure also:

Run SIP =

SUMX(FILTER(ALL('T_ORDER (For SIP)'[order_date]), 'T_ORDER (For SIP)'[order_date] <= MAX('T_ORDER (For SIP)'[order_date])), [Running SIP])

and both of them are not working for this problem statement, it is showing like this, need runnning total rather than this.

So is there any other logic in your mind which could be working in this scenario?

Thanks

Mihir 

[v-yueyunzh-msft]

Hi , @miiihiir 

According to your description, you want to calculate the rolling total from a measure?
If this , here are the steps you can refer to :
(1)This is my test data:

(2)We need to create a date table as  a dimension table:

Date = ADDCOLUMNS( 
CALENDAR(FIRSTDATE('Table'[Date]),LASTDATE('Table'[Date])),
"Year", YEAR ( [Date] ),
"Month", MONTH([Date]),
"Year_Month", year([Date]) * 100 + MONTH([Date])
)

And we need to create a relationship between two tables:

In my test measure is this:

My Measure = SUM('Table'[Value])

(3)Then we can create a measure like this:

Rolling Total = var _t = SUMMARIZE(ALLSELECTED('Date') ,'Date'[Year_Month] , "measure" , [My Measure])
var _cur_year_month = MAX('Date'[Year_Month]) 
var _t2 =FILTER(_t , [Year_Month]<=_cur_year_month)
return
SUMX(_t2, [measure])

Then we can get the rolling total:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Mihir · Accepted solution]

Try Using this measure:

Cummulative SIP =

CALCULATE([Running SIP], FILTER(ALLSELECTED('TableName'), 'TableName'[date] <= MAX('TableName'[date])))

[johnt75]

Try

Run SIP =
VAR MaxDate =
    MAX ( 'T_ORDER (For SIP)'[order_date] )
RETURN
    SUMX (
        FILTER (
            ALL ( 'T_ORDER (For SIP)'[order_date] ),
            'T_ORDER (For SIP)'[order_date] <= MaxDate
        ),
        [Running SIP]
    )