topic Re: Date only update when refresh in DAX Commands and Tips

Date only update when refresh

Anonymous — Wed, 14 Sep 2022 06:57:19 GMT

Hi,

I have the below equation which create a table for last 7 days.

However, I would like to know is there any method stop the powerBI calculating the latest "Today" when I open the file?

In other words when I last updated the file on 13/9, the "today" should stay at 13/9 even when I open the file today(14/9), so that the last 7 days record can align with the data period received during that 7 days.

Thank you.

Equation:

Table =

VAR _today = TODAY()

RETURN

UNION

(ADDCOLUMNS(CALENDAR(_TODAY - 7,_TODAY) , "FILTER", "Last 7 Days","Order",0))

Re: Date only update when refresh

ReneMoawad — Wed, 14 Sep 2022 07:37:48 GMT

Hi Anonymous,

Please follow the below steps to create a new table we use it to get the Last Refreshed Date

- Open Transform Data

- Click on New Source and choose Blank Query

- Click on Advanced Editor and insert the below script:

let
Source = #table(type table[Date Last Refreshed = datetime], {{DateTime.LocalNow()}})
in
Source

Those steps will create a table with 1 value which is the Last Refreshed Date, change the type of this column to Date

now in your measure, change the below formula

VAR _today = TODAY()

VAR _today = MAX('LastRefreshedTable'[Date])

This field will only refresh when you Refresh the table "LastRefreshedTable"

Re: Date only update when refresh

Anonymous — Thu, 15 Sep 2022 02:08:13 GMT

Hi ReneMoawad

I am new to this method and when I type in the equation you provided, the table is not creating, may you please share how you do it?

Re: Date only update when refresh

ReneMoawad — Thu, 15 Sep 2022 05:04:42 GMT

Re: Date only update when refresh

Anonymous — Thu, 15 Sep 2022 08:55:13 GMT

Thanks! The equation is working, I will further have a test tomorrow to see whether the date will only update on refresh.