Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all, I'm in the process of developing my second Power Bi Report and have an issue with my date slicer.
Its just a horizontal slicer with two buttons (Today and MTD).
If I run it on the desktop in the morning and select 'Today' its fine and it shows todays data however if I look at the report (via my phone and click 'Today' it continues to show the previous days data even though the data has refreshed (its scheduled), I have put a timestamp in my stored procedure to show when the schedule has run and it definitaly has run in the morning). If I redeploy the report again from the desktop it will then show todays data on my phone and works fine for the rest of the day.
To implement the slicer I created a table using the following DAX which I have obtained from an online tutorial which was originally for a MTD, QTD, YTD slicer. I have adjusted it to show Today and MTD only. I think my DAX for add columns for today might be the cause?
I have added this table into my relationships joining to my calendar table and having a Many to Many relationship which was outlined in the tutorial I used and as mentioned it works perfectly on the desktop.
Today/MTD Selection =
VAR TodayDate = TODAY()
VAR MonthStart = CALCULATE(STARTOFMONTH(Sales[DateEntered]),MONTH(Sales[DateEntered])=MONTH(TodayDate))
var Result =
UNION(
ADDCOLUMNS(CALENDAR(TodayDate, TodayDate), "Selection","Today"),
ADDCOLUMNS(CALENDAR(MonthStart, TodayDate), "Selection","MTD")
) RETURN Result
Any advice appreciated.
Thanks
Justin
Solved! Go to Solution.
Thanks for the reply Amit. I figured out the issue is because of the time zone difference. When published TODAY() was still returning yesterdays date when I run the report in the morning. What I have done is add a timestamp in my sql stored procedure so it will always use the date that the scheduled task has last run as today. I'll keep an eye on it over the next couple of days to ensure its working as expected.
Thanks
Justin
@JustinM123 , I think you are looking for a measure slicer.
I think you need to use a measure like
Measure =
var _sel = selectedvalue(Slicer[value])
return
if(_sel = "Today" , CALCULATE(SUM(Sales[Sales Amount]),Filter('Date', 'Date'[Date] today())) ,CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])))
Also refer
https://www.youtube.com/watch?v=b9352Vxuj-M
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
MTD using today, if needed
MTD Today =
var _min = eomonth(today(),-1)+1
var _day = datediff(_min, today(),day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max && 'Date'[Day of Year] <= _day) )
Thanks for the reply Amit. I figured out the issue is because of the time zone difference. When published TODAY() was still returning yesterdays date when I run the report in the morning. What I have done is add a timestamp in my sql stored procedure so it will always use the date that the scheduled task has last run as today. I'll keep an eye on it over the next couple of days to ensure its working as expected.
Thanks
Justin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |