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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Daniel_G 

With a date calendar, you can overall, month , qtr and year cumulative like this

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))


YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Daniel_G's avatar
Daniel_G
Frequent Visitor
6 years ago

Thanks for quick reply.

 

I know how to calculate cummulative values. My problem is that, if I apply drop down type slicer in my report, then it is reduced to selected dates. What I would like to achieve is to calculate cummulative sum up to date selected in the drop down filter, as oppose to using 'before' date filter.