Cumulative Total Help - Slicer sets starting date

[Nicho247]

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!

Cumulative OnTime =

COUNTROWS(

FILTER(

GENERATE(WKO,'Calendar'),

WKO[CompletedDate] <= MAX('Calendar'[Date])))

[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"))

[Nicho247]

Hello Ashish - No sir. This is similiar to last time with a flat bar (not running total).

[Ashish_Mathur]

Hi,

Is this what you want?  Download the PBI file from here.

Hope this helps.

[Nicho247]

Hello Ashish - No, this doesn't work. These are not running totals and is not affected by the slider. Did you upload the right file? Can you demonstrate how this answers my question? Unsure if I am missing something here.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Nicho247]

Hello Ashish - 

Here ya go. This is with some sample data.  Should be good enough to replicate the issue. Have a generic calendar table. Have another table with some dates. How to make the slider control the starting point for a running total/cumulative total.

Nick

https://1drv.ms/u/s!AlWk_fvRgbyDoDsGBjSdaNP6D7aS?e=5ezNqx

[v-gizhi-msft]

Hi,

For your sample02, please try this measure:

Measure = 
CALCULATE (
    COUNTROWS ( WKO ),
    FILTER (
        WKO,
        WKO[CompletedDate] <= MAX ( 'Calendar'[Date] )
            && WKO[CompletedDate] <> BLANK ()
    )
) + 0

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[Nicho247]

Hello Giotto - No, this doesn't work. It is no different than my initial submission. If I take your file and change the slider date, the running total calculate is unaffected. E.G. here is a pic from your file.

With a date of 3/20, the graph should start at 0, but is starting at 54. In other words the running total is calculated independently of the slider.

[v-gizhi-msft]

Hi,

Please try to create a duplicate calendar table first.

Choose calendar_2[date] as a slicer and calendar[date] as visual's x-axis. 

Then try this measure:

Measure = 
CALCULATE (
    COUNTROWS ( WKO ),
    FILTER (
        WKO,
        WKO[CompletedDate] <= MIN ( 'Calendar_2'[Date] )
            && WKO[CompletedDate] <> BLANK ()
            && WKO[CompletedDate] <= SELECTEDVALUE ( 'Calendar'[Date] )
    )
) + 0

Add this measure to the visual, the result shows:

See my attached pbix file.

Best Regards,

Giotto

[Nicho247]

Hello Giotto - This is an interesting idea with two calendar tables. Note, in your screenshot, the slider does not appear to control the graph nor the data. If it was doing either then the x-axis would reflect the slider dates and the running total wouldn't start so soon. Clearly is startin in January while the slider minimum is set to March. So this doesn't work.

[Nicho247]

Ok. Finally got it. Have to make a nested calculate to determine the minimum selected date from the slicer, then limit the wko rows by this min date and then start the running total.

MyCumulativeOnTime =

Calculate (

CountRows (

FILTER(

WKO, WKO[CompletedDate] >= CALCULATE(MIN('Calendar'[Date]),ALLSELECTED('Calendar'[Date])) && WKO[CompletedDate] <= MAX('Calendar'[Date]))))

[v-gizhi-msft]

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

[Nicho247]

Hello Giotto -

The first measure does create the running total, but it unaffected by the slicer data. 

The second measure also creates the running total but removes the ability to use the legend, and is unaffected by the slicer data.

😞

Nick

[amitchandak]

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

[Nicho247]

Bummer. 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.