cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sgupta22
Helper I
Helper I

Cumulative totals between slicer dates

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
1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1669947692521.png

(2)I create a calendar date like yours:

vyueyunzhmsft_1-1669947712377.png

(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:

vyueyunzhmsft_2-1669947792470.png

 

 

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

 

View solution in original post

5 REPLIES 5
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1669947692521.png

(2)I create a calendar date like yours:

vyueyunzhmsft_1-1669947712377.png

(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:

vyueyunzhmsft_2-1669947792470.png

 

 

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

 

Hi Aniya

 

This is perfect.  Thanks a ton!

djurecicK2
Memorable Member
Memorable Member

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.


If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
djurecicK2
Memorable Member
Memorable Member

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.


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

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])))

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.