Forum Discussion
DATEADD AND MAX
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
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,
KellyDid I answer your question? Mark my post as a solution!
3 Replies
- v-kelly-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!
- AnonymousNot applicable
Thanks for you help, it works!!
- amitchandakSuper User
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: