if with now()

[lamorim]

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.

I am using the formula

if[Data]=NOW()-1 then true else false

but I get the error "Expression error: The name 'NOW' wasn't recognized. Make sure it's spelled correctly."

What I am doing wrong? I look allover and can not find the solution.

Thanks 

[Anonymous · Accepted solution]

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]

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.