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

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!