Cumulative totals between slicer dates

[sgupta22]

Hi everyone,

I am currently using the following formula to compute the cumulative total for a field:

Cumulative PBT = CALCULATE([Adjusted PBT], FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date])))

However, this formula computes the cumulative total from the start of the model to the maximum date of the model.  I am also using a slicer for the years.  Is there a way I can compute the cumulative between two selected slicers for instance, between year 2016 and 2020?

Thanks

Shashank

[v-yueyunzh-msft · Accepted solution]

Hi , @sgupta22 

According to your description, you want to calculate the Cumulative total with the year slicer. Right?

Here are the steps you can refer to :
(1)This is my test data:

(2)I create a calendar date like yours:

(3)Then we can create a measure :

Measure = var _current_date = MAX('Calendar'[Date])
var _min_date = MINX( ALLSELECTED('Calendar') , [Date])
var _t  = FILTER( ALLSELECTED('Table') , 'Table'[Date] <= _current_date && 'Table'[Date] >= _min_date)
return
SUMX(_t , [Value])

(4)Then we put the 'Calendar'[Year] on the slicer visual and the 'Table'[Date], [Measure] on the table visual and then we can meet your need:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

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

[voodoo23]

Hello, Aniya, i have same problem , i do in my report like your example, but i need more explain on my data. Can you help me with this?

[sgupta22]

Hi Aniya

This is perfect.  Thanks a ton!

[djurecic]

Hi @sgupta22 ,

 Are you using that measure in a table or matrix with the year field? Maybe you can post an example of the end goal you are looking for.

[djurecic]

Hi @sgupta22 ,

 That DAX looks good. What tables/fields are you using in your time related slicers? You need to figure out why MAX('Calendar'[Date]) is not being affected by the slicers.

[sgupta22]

Thanks for the reply!  The slicer is using 'Calendar'[Year].

I guess the max condition is ok but when I combine it with the MIN condition, the formula doesn't seem to work.  For example, I tried to use the following formula, however, it returns the yearly numbers rather than the cumulative total from the minimum date:

Cumulative PBT = CALCULATE([Adjusted PBT], FILTER(ALL('Calendar'), 'Calendar'[Date] >= MIN('Calendar'[Date]) && 'Calendar'[Date] <= MAX('Calendar'[Date])))