Forum Discussion

Daniel_G's avatar
Daniel_G
Frequent Visitor
6 years ago
Solved

How to use dropdown slicer for running total

Hi Guys,   I'm working with data that requires showing cummulated totals up to certain date. I can easily do that with 'before' date slicer, but I would like to be able to simply select a month and...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    You can try to create a seperate slicer table, like this:

    Slicer table =
DISTINCT ( SELECTCOLUMNS ( 'Table', "Date", 'Table'[Date] ) )

    Then you can try this measure to show the cumulative total values:

    Cumulative values =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date] <= SELECTEDVALUE ( 'Slicer table'[Date] )
    )
)

    Hope this helps.

     

    Best Regards,

    Giotto

v-gizhi-msft's avatar
v-gizhi-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi,

 

You can try to create a seperate slicer table, like this:

Slicer table =
DISTINCT ( SELECTCOLUMNS ( 'Table', "Date", 'Table'[Date] ) )

Then you can try this measure to show the cumulative total values:

Cumulative values =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date] <= SELECTEDVALUE ( 'Slicer table'[Date] )
    )
)

Hope this helps.

 

Best Regards,

Giotto

Daniel_G's avatar
Daniel_G
Frequent Visitor
6 years ago

That actually works 🙂

 

I modified the formula, so it would work with category and location filters and tables, so it now looks like that:

Cumulative values = 
CALCULATE (
SUM ( Sales[Actual] ),
FILTER (
ALLEXCEPT(Sales,Sales[Category],Sales[Sub-category],Sales[Country],Sales[Region]),
Sales[Date] <= SELECTEDVALUE ( 'Calendar'[Dates] )
)
)

 Thanks Giotto