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.
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?
Thanks!
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
Regards
Vijay Perepa
Proud to be a Super User!
Thanks! if you find my answer as solution please mark that as Solution. Thanks again!
Proud to be a Super User!
Hi @VijayP ,
Great solution but please check this post with some additional information about using last date.
https://www.sqlbi.com/articles/understanding-the-difference-between-lastdate-and-max-in-dax/
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsAgree with you! But for current Problem statement can be fixed with LastDay appropriatelly as per my understanding!
What do you say! Thanks for Feedback! 🙂
Proud to be a Super User!
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.
👍
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThose are good solutions! Thank you both. @MFelix @VijayP
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:
ID | Date | Result |
111 | 16/9/2020 | Positive |
222 | 17/9/2020 | Positive |
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
Proud to be a Super User!
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |