Year over Year calculation Issue

[Anonymous]

 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!

[v-frfei-msft]

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

[bidevsugmen · Accepted solution]

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]

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]

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.

[Anonymous]

@bidevsugmen

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

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