DATEADD AND MAX

[Anonymous]

Hello,

I am in trouble with DATEADD DAX function.

For now, I have two functions which are running efficiently.

Current Month default phase = CALCULATE(SUM('public costs phase'[Heures mensuelles]),FILTER('public costs phase','public costs phase'[Phase de projet]=RELATED('public financial-overview'[Phase par défaut]) && 'public costs phase'[Monthly Date]<=MAX('public costs phase'[Monthly Date])))         (month M)

Last Month default phase = CALCULATE(SUM('public costs phase'[Heures mensuelles]),FILTER('public costs phase','public costs phase'[Phase de projet]=RELATED('public financial-overview'[Phase par défaut]) && 'public costs phase'[Monthly Date]<MAX('public costs phase'[Monthly Date])))            (month M-1)

The first function gives me an amount of hours for the current phasis of an architectural project, for the current month.  The seond gives me the same for the month before.

Now i have to do the same exercise, but for the months (M-2) and (M-1).  I though to use the DATEADD function, to substract one month : 

Current Month default phase = CALCULATE(SUM('public costs phase'[Heures mensuelles]),FILTER('public costs phase','public costs phase'[Phase de projet]=RELATED('public financial-overview'[Phase par défaut]) && DATEADD('public costs phase'[Monthly Date],-1,month)<=MAX(DATEADD('public costs phase'[Monthly Date],-1,month))))

When i try that, it returns me an error telling me that MAX argument must be a column...And DATEADD returns a table with a single column...

Could you help me to find the right way to do that?  Am i wrong?  Am I using the right method?

Thanks for your help, regards,

Mike

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Modify the expression of "MAX(DATEADD('public costs phase'[Monthly Date],-1,month))" as below:

MAXX(DATEADD('public costs phase'[Monthly Date],-1,month),[Monthly Date])

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thanks for you help, it works!!

[amitchandak]

@Anonymous , You can use date as dateadd

example  measure = //subract 12 months

var _max = max(Date[Date])

return

Date(year(_max), month(_max)-12, day(_day))

refer:

https://www.youtube.com/watch?v=YWo-ZpKM6gU

https://www.youtube.com/watch?v=9qiRivlBv8w