Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I want the report I am creating to always show the data since 8am, we are a 24hour business so at 2am, I need it to show the data we have since 8am the day before. This means the relative date filtering does not work for me.
I have been using Crystal previously and this would have been easy to setup, any ideas on the best way to get PowerBI to do this?
Thanks in advance,
Malcolm
Solved! Go to Solution.
Hi
All seems to be working great except that when I refresh the report in the PowerBI service the report is missing all the current hour's data. In the Desktop app it shows the data correctly and even the pinned table from the report in a dashboard shows the current hours information but the report itself in the power BI service will not show the current hours data it just shows all information up to the latest hour.
I hope that makes sense. I thought it might be a UTC issue but I cannot see anything that would stop the data, especially as the pinned table is updating correctly on the dashboard.
Many Thanks
Malcolm
Update - I seem to have fixed it by placing +(1/24) after NOW ()
+ (1/24) being my timezone
Hi MalOSB,
As a general solution, you can create a measure like this pattern to achieve your requirement:
Result = VAR Eight_Am_Of_LastDay = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 1 ) + TIME ( 8, 0, 0 ) RETURN CALCULATE ( aggregation, FILTER ( Table, Table[DateTime] <= NOW () && Table[DateTime] >= 8am_Of_LastDay ) )
Hope it's helpful to you.
Jimmy Tao
Thank you very much. I will try it now
Malcolm
PowerBI says that "aggregation" is not a valid Table, variable or function name?
Hi MalOSB,
It's just a pattern, aggregation should be replaced by the aggregation function(e.g.: SUM(), MAX(), MIN(), COUNT()) you want to use to achieve the aggregated value.
Regards,
Jimmy Tao
Thanks Jimmy
Since I want the whole report to run from this date/time I wanted to put it in the page filter? or would I have to make each visual use the DAX expression?
Thanks again,
Malcolm
Hi MalOSB,
Page level filter can only implement simple filter operation like "great than", "less than", "is not" and so on. So in your senario I'm afraid you should measure instead of page level filter.
Regards,
Jimmy Tao
Hi
All seems to be working great except that when I refresh the report in the PowerBI service the report is missing all the current hour's data. In the Desktop app it shows the data correctly and even the pinned table from the report in a dashboard shows the current hours information but the report itself in the power BI service will not show the current hours data it just shows all information up to the latest hour.
I hope that makes sense. I thought it might be a UTC issue but I cannot see anything that would stop the data, especially as the pinned table is updating correctly on the dashboard.
Many Thanks
Malcolm
Update - I seem to have fixed it by placing +(1/24) after NOW ()
+ (1/24) being my timezone
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |