Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Date only update when refresh

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's avatar
    ReneMoawad
    3 years ago

    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"

4 Replies

  • ReneMoawad's avatar
    ReneMoawad
    Resolver III
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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?