Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |