Forum Discussion
Introducing Month field from Calendar changes YTD calculation
- 7 years ago
Hi
I added the RETURN command, and it is working perfectly!
Many thanks
Joe
Then let's say you have a filter to limit to only July. Then your calculation is run in that context, thus finding the max year associated with July: 2018.
If you want the max year from the calendar table, and ignore filters, you can do something like:
Sales YTD = var max_year = CALCULATE(MAX(Calendar2[Year]), ALL(Calendar2)) return CALCULATE([Ttl Sales], //***Edit: Had forgotten to include "return"// FILTER(trans,year(trans[DATE])=max_year), FILTER(trans,trans[Hold?]="n"))
Note: this is not a typical YTD function, as YTD usually refers to the cumulative value each month.
Hi
Thank you for your explanation of the error and formula suggestion.
I'm getting this error:
The syntax for 'CALCULATE' is incorrect. (DAX(var max_year = CALCULATE(MAX(Calendar2[Year]), ALL(Calendar2))CALCULATE([Ttl Sales],FILTER(trans,year(trans[DATE])=max_year),FILTER(trans,trans[Hold?]="n")))).
Any ideas?
- JoeAPH7 years agoRegular Visitor
Hi
I added the RETURN command, and it is working perfectly!
Many thanks
Joe