YOY calculation - last year data not populating

[archuleta28]

Hi,

New to DAX calculations, need your kind help.

I need to show comparison of data with the current year selected in the dropdown to its previous year. Here, first I was trying to calculate YOY comparision so I had made two measures:

LogisticCost LastYr = CALCULATE(SUM('sheet'[Actual Cost]), DATEADD('sheet'[Invoice Date],-1,YEAR))

LogisticCost CurrentYr = SUM('sheet'[Actual Cost])

But, now the Last year value is not populating : 

Can anyone please help me why is this happening and how this can be corrected?

Thanks.

[v-yinliw-msft · Accepted solution]

Hi @archuleta28 ,

The LY is blank because it is based on the current date. Because I lack the data.

So I create an sample table to explain it.

If I use the formula you give, it will show like this:

The data was calculated by the current year, not the last year.

So even if it has the true number, but in the wrong position.

You can try this method:

In my example, I set a date to separate the two fiscal year. I choose the Feb 1^st, just an example, you can change it to what you need.

New measure LY:

LY =

-- set the Feb 1st as the boundary of every fiscal year

var _startdate = DATE(YEAR(TODAY()) - 1, 2,1)

var _Current = DATE(YEAR(TODAY()), 2 ,1 )

return

    CALCULATE(SUM('sheet'[Actual Cost]), FILTER('sheet', 'sheet'[Invoice Date] >= _startdate && 'sheet'[Invoice Date] <= _Current))

The result is:

Hope this helps you. Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[archuleta28]

Thank you so much for your help, it worked!  Really appreciate your efforts:)