DAX Year on Year Calculation

[KH_Mike]

Hi All,

I have a table like below and try to use below DAX to calculate last year record for Year on Year comparison.

Last Year =
var LY=SELECTEDVALUE(Table[Year])-1
Return
CALCULATE(sum(Table[Volume]),Table[Year]=LY)

When I use the slicer to select Year = 2021

The DAX formula will show 120. However, what I want to show is 40 because Year 2021 only contain 1, 2, 3, 4 in Month. Thank you.

Year	Month	Volume
2020	1	10
2020	2	10
2020	3	10
2020	4	10
2020	5	10
2020	6	10
2020	7	10
2020	8	10
2020	9	10
2020	10	10
2020	11	10
2020	12	10
2021	1	10
2021	2	10
2021	3	10
2021	4	10

[Nathaniel_C · Accepted solution]

Hi @KH_Mike , try this:

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Last Year = 
var LY=SELECTEDVALUE(myTable[Year])-1
var TY= Selectedvalue(myTable[Year])
var _maxMonth = CALCULATE(MAX(myTable[Month]),myTable[Year]=TY)
var _calc = CALCULATE(sum(myTable[Volume]),myTable[Year]=LY && myTable[Month]<=_maxMonth)




Return _calc

[CNENFRNL]

Construct a Date column in order to leverage time intelligence functions,

[KH_Mike]

Hi @CNENFRNL 

Is there any option to acheive the same result? I don't want to creat new column for this issue. Thank you.

[Nathaniel_C]

Hi @KH_Mike ,
Like this?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[KH_Mike]

Hi @Nathaniel_C 

Yes. Can you share me the DAX? Thank you.