Forum Discussion

lamorim's avatar
lamorim
Frequent Visitor
9 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

Framet's avatar
Framet
Resolver II
9 years ago

Hi,

 

From the sounds of it you are adding this column during the data load in 'M'?

 

In which case the error is correct as NOW() is a DAX function and not correct for 'M'.

 

One possible equivilent that might work for you is DateTime.LocalNow.

https://msdn.microsoft.com/en-us/library/mt296606.aspx

 

It might be worth mentioning that if you do this the comparison is only made at the point the data is refreshed which may not be a big deal if this is done daily in the morning but worth remembering.

 

Personally and this is just my preference, I would create a measure in your data model using DAX that returns blank if the data of the transaction in your data isn't within the last 30 days of NOW(). This would be evaluated each time the report was refreshed and therefore always be the last 30 days.