topic DAX Year on Year Calculation in DAX Commands and Tips

DAX Year on Year Calculation

KH_Mike — Wed, 02 Jun 2021 02:53:26 GMT

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

Re: DAX Year on Year Calculation

CNENFRNL — Wed, 02 Jun 2021 03:48:02 GMT

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

Re: DAX Year on Year Calculation

Nathaniel_C — Wed, 02 Jun 2021 04:05:48 GMT

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

Re: DAX Year on Year Calculation

KH_Mike — Wed, 02 Jun 2021 05:19:53 GMT

Hi CNENFRNL

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

Re: DAX Year on Year Calculation

Nathaniel_C — Wed, 02 Jun 2021 13:56:56 GMT

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

Re: DAX Year on Year Calculation

KH_Mike — Thu, 03 Jun 2021 00:54:59 GMT

Hi Nathaniel_C

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