Forum Discussion

InsightsByV's avatar
InsightsByV
Icon for Super User rankSuper User
3 years ago
Solved

YOY calculation - last year data not populating

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.
Need Help
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi InsightsByV ,

     

    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 1st, 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.

2 Replies

  • v-yinliw-msft's avatar
    v-yinliw-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi InsightsByV ,

     

    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 1st, 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.

    • InsightsByV's avatar
      InsightsByV
      Icon for Super User rankSuper User
      3 years ago

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