Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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
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
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
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/
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.
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |