Hello! I have a quick question about "dinamic" dates.
I have a report that I refresh once a day (at night) and then I upload it to the Report Server for the final users to see.
The problem is that I have many measures that use TODAY() in their formula, but if this users open the report the next day, they won't be able to see the data I need them to see. They will visualize blank tiles everywhere.
The quick solution is to write all my measures with fixed dates, e.g CALCULATE(DISTINCTCOUNT( ID ) ; DATESBETWEEN( Date; DATE(2020;9;18);DATE(2020;9;17))).
But this is totally impractical because I will have to change the dates every day I refresh the data.
Is there a way in the in which I can choose the TODAY() date to be fixed in the Report Server?
Instead of using Today() , try using LastDAy(FAct Table) which will give the avaialble last day so that it is dynamic and also showing the latest information
If you find this as solution please mark as solution and share your Kudos
Hi @VijayP ,
Great solution but please check this post with some additional information about using last date.
Agree with you! But for current Problem statement can be fixed with LastDay appropriatelly as per my understanding!
What do you say! Thanks for Feedback! 🙂
Totally agree with you especcially because it's used in calculated syntax, just pushing some additional information since it's a very recent article that explains the usage of this calculation.
I could use LastDAy(FAct Table) or MAX(date) but I will have another problem.
For e.g if use LASTDAY/MAX to calculate the Positive Covid tests of the last day, and I don't have any then the tile will show me the total of the last day that had positives.
Let me show it with an example:
In this case when I refresh my report today (18/9/2020) the "Positives last day" tile will show me 1. But the reality is that i have no positives.
In that case your report is not showing the real last Day as it doesnt have today's Date. You need to get that information inside the Fact Table to have the logic worked
Hi @mficco ,
Believe that you need to check what values you need to calculate at the MAX/LASTDATE calculations and the ones that you need with TODAY().
Has you refer in the original post when you use the TODAY you get blank values, but to what I can understand from this last post in some cases you really want the blank or a zero, so you need to check case by case.
Hi @mficco ,
To what I can understand you need to pick up the latest date correct? I would suggest that instead of using the TODAY() you would use something like MAX(Table[Date]) so you would pick up the maximum value of the date available in your fact table.
Using that you would always get the latest information.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.