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.
Hello,
I am trying to calculate the sales for the past year( going back a year, not civil year), using a slicer.
This means that when I choice in a slicer dates between (23-01-2014 and 23-02-2016) I want the formula to read only the bigger value (23-02-2016). This I got the measure to do it with:
FILTER(all('date table'[Date]);'date table'[Date]<=MAX('date table'[Date])
But now I need to get it to go back one year and I tried :
FILTER(all('date table'[Date]);'date table'[Date]<=DATEADD(MAX('date table'[Date]);-1;YEAR)))
But I get the following error:
Can anyone help me?
Thank you
Inês Costa
Solved! Go to Solution.
Hi @inescosta,
Because dates part in DateAdd() function requires a column, while Max() returns aggregated values. It's not supported to use MAX() function in DateAdd() function as dates part. Please replace the
FILTER(all('date table'[Date]);'date table'[Date]<=DATEADD(MAX('date table'[Date]);-1;YEAR)))
as below:
FILTER(ALL('date table'[Date ]);'date table'[Date ]<=DATE(YEAR(MAX('date table'[Date ]))-1;MONTH(MAX('date table'[Date ]));DAY(MAX('date table'[Date ]))))
Best Regards,
Qiuyun Yu
It's great that this answer helped but adding or subtracting months means you can't keep the same day value across all months and have it be correct. Another approach to avoid this problem is to use MINX() on the date table and use dateadd or EOMONTH to create an offset date from the date table values and then take the minimum offset value.
Hi @inescosta,
Because dates part in DateAdd() function requires a column, while Max() returns aggregated values. It's not supported to use MAX() function in DateAdd() function as dates part. Please replace the
FILTER(all('date table'[Date]);'date table'[Date]<=DATEADD(MAX('date table'[Date]);-1;YEAR)))
as below:
FILTER(ALL('date table'[Date ]);'date table'[Date ]<=DATE(YEAR(MAX('date table'[Date ]))-1;MONTH(MAX('date table'[Date ]));DAY(MAX('date table'[Date ]))))
Best Regards,
Qiuyun Yu
thanks! that helped me out! I have a graph that shows how a products was sold 3 months before, during and 3 months after a campaign.
I had this filter: FILTER('Date 2';'Date 2'[Date]>=DATEADD(Campaign[Starting Date];-3;MONTH)&&'Date 2'[Date]<=DATEADD(Campaign[Ending Date];3;MONTH)))
but it didnt work for recent campaigns because there was no data yet for the 3 months in the future. So the line graph didnt work. Now it just stops when it doesnt have more data.
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 |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |