Forum Discussion
Helper IIICumulative Total Help - Slicer sets starting date
Hello -
Working on a basic running/cumulative total. I have it working but it is grabbing all dates in my table. I'd like it to compute starting from the date present in a slicer. I've poked around quite a bit and unable to find an easy solution. As I have written the formula I can see the problem...how the code is going to go count everything. Hitting a wall with trial & error involving filters. Any help would be awesome!
The WKOCompletedDate goes back to 1/1/2019, and for example, my calendar date slicer is going from 1/1/2020. The graph is clearly controlled by the slicer but the calculated #'s are clearly starting from 1/1/2019. I believe I need to modify the below code to invoke some MIN function or SELECT/ALL SELECT. Ack!
19 Replies
- amitchandak
Super User
Nicho247 , Make sure you have all the dates in your calendar. Try
Cumulative OnTime =
calculate(COUNTROWS(WKO),
FILTER(all(WKO), WKO[CompletedDate] <= MAX('Calendar'[Date])))or
Cumulative OnTime =
calculate(COUNTROWS(WKO),
FILTER(allselected(WKO), WKO[CompletedDate] <= MAX('Calendar'[Date])))
Nicho247Bummer. Neither of those work. No different than what mine was doing, but both drop the ability to breakdown the running total by legend (aka only 1 line).
The beginning of my dataset on WKO table is 1.1.2019.
Here is pic1. My date slicer also reads 1.1.2019. Cumulative totals on all start at 0.
Here is pic2. My date slicer has been changed to 1/1/2020. Cumulative totals should start at 0 but dont.
- v-gizhi-msft
Community Support
Hi,
Please try to delete the relationship between 'WKO' table and 'calendar' table.
Then try this measure:
Cumulative OnTime = CALCULATE ( COUNTROWS ( WKO ), FILTER ( WKO, WKO[CompletedDate] >= SELECTEDVALUE ( 'Calendar'[Date] ) ) )Hope this can help.
Best Regards,
Giotto
- Nicho247
Hello Giotto - Thank you for your recommendation. Unfortunately this provides a bizarre reverse cumulative total. It does appear to be affected by the date sliders but not in the inteded fashion. Unsure if it is also counting from the data set or from the sliders. Visually the slider is taken into account.
- v-gizhi-msft
Hi,
Please try this:
Cumulative OnTime = CALCULATE ( COUNTROWS ( WKO ), FILTER ( WKO, WKO[CompletedDate] <= SELECTEDVALUE ( 'Calendar'[Date] ) ) )Or this:
Cumulative OnTime = CALCULATE ( COUNTROWS ( WKO ), FILTER ( ALLSELECTED ( WKO ), WKO[CompletedDate] <= SELECTEDVALUE ( 'Calendar'[Date] ) ) )Best Regards,
Giotto
- Ashish_Mathur
Hi,
Does this measure work?
=CALCULATE(COUNTRWS(WKO),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date]),TODAY()))
Ensure that the Data slicer is built from the Calendar table and you select 1/1/2020 there.
Hope this helps.
- Nicho247
Hello Ashish -
Thank you for your recommendation. Unfortunately this is just outputting flat lines across all times. This is not a running total.
- Ashish_Mathur
Hi,
Does this measure work?
=CALCULATE(COUNTROWS(WKO),DATESYTD(Calendar[Date],"31/12"))
