cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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!

9 REPLIES 9
Highlighted
Super User III
Super User III

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Solution Sage
Solution Sage

@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

Highlighted

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted

@mficco 

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

Highlighted

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

Highlighted

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Frequent Visitor

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:

IDDateResult
11116/9/2020Positive
22217/9/2020Positive

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.

Highlighted

 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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted

@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

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors