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-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

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

3 Replies

  • 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!
  • 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.