Forum Discussion

lamorim's avatar
lamorim
Frequent Visitor
10 years ago
Solved

if with now()

Good Morning   I am trying to filter a dashboard to show only the last 30 days and yesterday numbers. I had read that this is only possible creating a column with true/false using the "if" formula...
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi lamorim,

     

    You can achieve your requirement with power query, I have used the Date.From(), DateTime.LocalNow(), Date.AddDays() functions, below is the sample:

    Test table: ID, Name, Date(random between 2016.1.1 ~ 2016.9.19)
     

     

    Open the advanced Editor and add formula about filter date:

    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >=Date.From(Date.AddDays(DateTime.LocalNow(),-30)) and [Date] <= Date.From(DateTime.LocalNow()))

     


     

    Click “Close & Apply” to save these operations.

     

    In addition, you can also use DAX to achieve your requirement:

    Table = CALCULATETABLE(Sheet1,FILTER(Sheet1,if(AND(Sheet1[Date]>=NOW()-30,Sheet1[Date]<=NOW()),TRUE(),FALSE())))
     


    Regards,
    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi lamorim,

 

You can achieve your requirement with power query, I have used the Date.From(), DateTime.LocalNow(), Date.AddDays() functions, below is the sample:

Test table: ID, Name, Date(random between 2016.1.1 ~ 2016.9.19)
 

 

Open the advanced Editor and add formula about filter date:

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] >=Date.From(Date.AddDays(DateTime.LocalNow(),-30)) and [Date] <= Date.From(DateTime.LocalNow()))

 


 

Click “Close & Apply” to save these operations.

 

In addition, you can also use DAX to achieve your requirement:

Table = CALCULATETABLE(Sheet1,FILTER(Sheet1,if(AND(Sheet1[Date]>=NOW()-30,Sheet1[Date]<=NOW()),TRUE(),FALSE())))
 


Regards,
Xiaoxin Sheng