cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User I
Super User I

@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





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

Proud to be a Super User!




@mficco 

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





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

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


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





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





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

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


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





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.

@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

 





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

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


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





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





Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors