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
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

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

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

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