Cumulative Total

[Anonymous]

Good morning People

I am struggling with a cumulative formula. The following formula calculates my total mass for each trading day perfectly. How do I convert it to a cumulative formula:

CALCULATE (
SUM ( Master[Mass(kg)] ),
          FILTER (
          Master,
          Master[Trading Day]
          <= MAXX ( FILTER ( Master, Master[Sales Year] = 2018 ), Master[Trading Day] )
          && Master[Sales Year] = 2017
)
)

Thank you

Herbz

[v-caliao-msft · Accepted solution]

@Anonymous,

For the cumulative sum, you could try with the DAX fomula in a new measure like:

Cumulative Sum =
CALCULATE (
    SUM ( Master[Mass(kg)]),
    FILTER (
        ALL ( Master[Trading Day],Master[Sales Year]),
        Master[Trading Day]<= MAXX ( FILTER ( Master, Master[Sales Year] = 2018 ), Master[Trading Day] )
          && Master[Sales Year] = 2017
    )
)

A sample result I got is like:

  

Regards,

Charlie Liao

[Anonymous]

Perfect solution, thank you very much Charlie