Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Year over Year calculation Issue

 I'm trying to show the variance year over year on the graph.

Here's my time table and a column I added to indicate the year status

 

Here're my measures

CYSpend = CALCULATE([Total Spend], Inv_Period[YoY] = 0)

PYSpend = CALCULATE([Total Spend], Inv_Period[YoY] = -1)

delta = [CYSpend] - [PYSpend]

 

What I ended up having is here:

 

 

As you can see, the delta is the same as 2018's spend.

Any help, idea or advice would be appreciated!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous

     

    The issue here is that the legends (FY) you have used is slicing the value in your measure delta.

     

    When you use the measure delta v/s months, their is no need of legend as you have already defined the logic as difference between Total spend of current and previous year.

     

    If you also want to display the individual values whose delta is being calculated, pull in the measures CY Spend and PY Spend in the line chart. This should solve your issue.

     

     

    Thanks,

    Suguna.

     

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for the reply but I think that didn't address my question

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous

     

    The issue here is that the legends (FY) you have used is slicing the value in your measure delta.

     

    When you use the measure delta v/s months, their is no need of legend as you have already defined the logic as difference between Total spend of current and previous year.

     

    If you also want to display the individual values whose delta is being calculated, pull in the measures CY Spend and PY Spend in the line chart. This should solve your issue.

     

     

    Thanks,

    Suguna.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Suguna,

       

      Thank you very much for the reply. After removing the legend it works. Could you kindly explain why the legend would screw my measure? And it's very strange that if I put filters on FY column instead of YOY, it also works.

      So I have 

      CYSpend = CALCULATE([Total Spend], Inv_Period[FY] = 2018)

      PYSpend = CALCULATE([Total Spend], Inv_Period[FY] =2017)

       

      What's difference between them?

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I guess I didn't describe clearly, so if I change my previous measures to these two, and keep everything else the same( still have FY as legends). The delta shows right answer on the chart. I mean the issue is solved and I'm just trying to understand more about PowerBI.

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous,

     

    Please try to update the measure as below. If it doesn't meet your requirement, kindly share your pbix to me.

     

    delta = IF(ISBLANK([PYSpend]),BLANK(),[CYSpend] - [PYSpend])

    Regards,

    Frank