Forum Discussion

inescosta's avatar
inescosta
Advocate II
9 years ago
Solved

dateadd and max error

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-201...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    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

leathard's avatar
leathard
Advocate I
3 years ago

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.