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,

 

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

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