Running total of measure

[Anonymous]

Hi all

I have been googling and reading posts a lot, but I still can't find a solution.

The root of my problem is probably the way the running total is calculated, but I can't find the reason.

Year	Month	Day	LJ_MAX_SUM	RT_LJ_MAX_SUM	What I want
2020	January	1	917	917	917
2020	January	2	907	1731	1824

The data of the last 5 years:

Year	Month	Day	Sum per day
2016	January	1	543
2016	January	2	664
2017	January	1	515
2017	January	2	585
2018	January	1	712
2018	January	2	907
2019	January	1	917
2019	January	2	814
2020	January	1	1104
2020	January	2	1180

LJ_MAX_SUM= 
VAR Temp=
UNION(
ROW("test2", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-1,YEAR))),
ROW("test3", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-2,YEAR))),
ROW("test4", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-3,YEAR))),
ROW("test5", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-4,YEAR))),
ROW("test6", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-5,YEAR))),
ROW("test7", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-6,YEAR))),
ROW("test8", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-7,YEAR))),
ROW("test9", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-8,YEAR))),
ROW("test10", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-9,YEAR))),
ROW("test11", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-10,YEAR)))
)
RETURN
MAXX(Temp, [test2])/1000

RT_LJ_SUM_MAX = CALCULATE (
   [LJ_MAX_SUM],
    FILTER (
        ALLSELECTED(  'Calendar_future'),
        'Calendar_future'[Date] <= MAX ( 'Calendar_future'[Date] )
    )
)

The measure ''RT_LJ_SUM_MAX' takes the green and blue marked value and calculates the running total. What I want is, that it takes the green and red value to calculate the running total.

How can I do this?

Really appreciate your help.

[Icey · Accepted solution]

Hello @adrianrupp ,

Try this:

RT_LJ_SUM_MAX =
SUMX (
    FILTER (
        ALLSELECTED ( 'Calendar_future' ),
        'Calendar_future'[Date] <= MAX ( 'Calendar_future'[Date] )
    ),
    [LJ_MAX_SUM]
)

Or this:

RT_LJ_SUM_MAX =
SUMX (
    FILTER (
        ALLSELECTED ( 'Calendar_future' ),
        'Calendar_future'[Date] <= MAX ( 'Calendar_future'[Date] )
            && Calendar_future[Date]. [Year] = MAX ( Calendar_future[Date].[ Year] )
    ),
    [LJ_MAX_SUM]
)

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Hi @Icey 

Thank you for the reply!

Both solutions work!

Best regards,

Adrian