Forum Discussion

Nicho247's avatar
Nicho247
Icon for Helper III rankHelper III
6 years ago

Cumulative 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 aro...
Need Help
v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Nicho247
Icon for Helper III rankHelper III
6 years ago

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's avatar
    Nicho247
    Icon for Helper III rankHelper III
    6 years ago

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