Static Varable

[dbaksa]

I would like to build a variables in my dashboard that stay fixed until the next data refresh. I have a dashboard that displays information for specific calendar periods, updated at 2PM daily. The next day before 2PM when I open the dashboard some of the information is not displayed because the expressions re-evaluate the DAX variables upon re-opening the dashboard. Is there a way to set a global variables that only update when the data is refreshed and not when the dashboard is re-opened prior to data update.

Thanks,

Dan B.   

[dbaksa]

I created the table with Power Query as advised and adjusted my DAX VAR expression to use the table data from the query table. The updated expressions worked as expected. When I reopened the dashboard the next day before 2PM the Power Query was re-evaluated and the prior days data was no longer visable. I did not refresh the dashboard and its obvious the Power Query table was refreshed when the dashboard was opened.

Table Query:

CurrentDay = {datevalue(format(today()-1,"MM/DD/YYYY"))}

[V-lianl-msft · Accepted solution]

Hi @dbaksa ,

Is the dashboard in service or desktop?

The data will be refreshed every time when the power bi desktop is reopened.

If the scheduled refresh is not configured in the service, the data will not be refreshed automatically.

The calculated column and the query column in power query are updated every time the data is refreshed, but measure will calculate dynamically according to the current context.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dbaksa]

Thanks I will give that a try!!

[dbaksa]

I did consider that solution! If I setup a manual table the data will be fixed and never change. I want to use TODAY() to evalute the dates based on current day, CurrDay = Today()-1 and PrevDay as TODAY-2. If I use a table I need to have a source (Excel Table, or Process a DB table) that evaluates the date and then refresh PowerBI to update the table of new source data. I work with another tool that allows global variables to be evaluated at refresh time LET v_CurrDate = TODAY()-1. They remain static as long as the refresh does not take place. If I update the dashboard at 2PM today and open the dashboard tomorrow before 2PM the dates still represent yesterday's refresh and all data is intact until the next refresh. Unless I'm missing something a new table will not solve my problem without a process to create the new dates in an updated excel spreadsheet or a DB process to update table dates and import the new dates to PowerBI.

Thanks for your response to my post

[freginier]

You can create today in power query with 

DateTime.LocalNow()

So it will update the date every time

[freginier]

You need to create a table in Power Query with your variable then it will be re-evaluate only after data refresh