dateadd and max error

[inescosta]

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

[leathard]

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.

[v-qiuyu-msft · Accepted 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

[Anonymous]

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. 

FILTER('Date 2';'Date 2'[Date]>=DATEADD(Campaign[Starting Date];-3;MONTH)&&'Date 2'[Date]<=date(YEAR(MAX(Campaign[Ending Date]));MONTH(MAX(Campaign[Ending Date]))+3;DAY(MAX(Campaign[Ending Date])))))

thanks a lot!