Forum Discussion
Using a slicer to select effective date without filtering out results
Hi, I'm attempting to build a report in which users can select totals across preset periods (let's say 1 day, 5 days, month-to-date, year-to-date for example) effective a selected date via single-date slicer. So if let's say the user selects Jul 31, 2020 then it will calculate a 1 day value, 5 day value, MTD value, etc as of Jul 31, 2020. Problem is the way I've built it, the slicer is filtering out the page which I understand is probably by design, but how would one get around this issue?
Thanks in advance for any suggestions.
Afkkek , if you want 1day, 5day, MTD rolled on 31st you will be able to do with a date table and time intelligence. If you want to display trend with help from 1 date then refer to the video at last with an independent table
example
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))5th Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-5))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))Rolling 5 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-5,Day))
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKADay Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9One date in slicer and additional date in trend : https://www.youtube.com/watch?v=duMSovyosXE
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))
2 Replies
- amitchandak
Super User
Afkkek , if you want 1day, 5day, MTD rolled on 31st you will be able to do with a date table and time intelligence. If you want to display trend with help from 1 date then refer to the video at last with an independent table
example
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))5th Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-5))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))Rolling 5 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-5,Day))
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKADay Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9One date in slicer and additional date in trend : https://www.youtube.com/watch?v=duMSovyosXE
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date])) Thanks a lot, that worked!
