Cumulative Sum with Filters

[Anonymous]

Hi

I need the cumulative sum of a measure.

I have a formula that gaves me that value. But when I apply a date filter, the formula is still using as the first value, the first date loaded in the data model and not the first one filtered.

This is an example:

Without filtered dates:
Date                   Value    Acumulated
01-01-2020        100             100
02-01-2020        200             300
03-01-2020        200             500
04-01-2020        200             700
05-01-2020        200             900

With filtered dates:
Date             Value         Acumulated        NeededValue
04-01-2020    200              700                       200
05-01-2020    200               900                      400

This is the formula I am using:

CumulativeSum=

CALCULATE(

           [Measure];

           FILTER (

           ALL ( Calendar[Date] );

          Calendar[Date] <= MAX ( Calendar[Date] )

           )

)

How can I get the "NeededValue" column?

Thanks!!

BTW: What happened to the "Insert Photos" in the forum? it was very easy to upload a image.. now it's a mess!

[v-diye-msft]

Hi @Anonymous 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[Jamesmathew]

@Anonymous  I forgot to mention, you should use ALLSELECTED insted of ALL as @Anonymous  suggested. I used ALLSELECTED in the sample file that I provided.

[Anonymous]

It looks like you need ALLSELECTED()

Like this:

Measure = CALCULATE(SUM('Table'[Values]),FILTER(ALLSELECTED('Table'),'Date'[Date]<=MAX('Date'[Date])))

Best wishes.

[Jamesmathew]

Hi @Anonymous ,

I beleive you are using date filter from fact table, you should use it form calender table. I tested with your measure and it's working when I use Filter from calender date.

Here is the sample PBI 

https://1drv.ms/u/s!AlTyyZ_9D_WjixfqrG60aoof1feq