Can TODAY() be fixed when uploading the report to the Server?

[mficco]

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!

[VijayP]

@mficco 

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

[VijayP]

@mficco 

Thanks! if you find my answer as solution please mark that as Solution. Thanks again!

[MFelix]

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/ 

[VijayP]

@MFelix 

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! 🙂

[MFelix]

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.

👍

[mficco]

Those 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.

[VijayP]

@mficco 

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

[MFelix]

 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.

[MFelix]

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.