Date only update when refresh

[Anonymous]

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))

[ReneMoawad · Accepted solution]

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()

To

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

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

[ReneMoawad · Accepted solution]

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()

To

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

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

[Anonymous]

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?

[ReneMoawad · Accepted solution]

[Anonymous]

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