How to use dropdown slicer for running total

[Daniel_G]

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 have all values calculated as a total for up to end of selected month.

Example:

date; amount

1/1/2020; 1

23/1/2020; 3

15/4/2020; 5

4/5/2020; 6

After selecting date 'April 2020' I should get total of 9.

Currently I would get 5, because drop down is filtering out all other months.

Any thoughts?

[v-gizhi-msft · Accepted solution]

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]

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

[amitchandak]

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

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.