Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

New Bitmap Image (2).jpg

 

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:

 

 

delta.jpg

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

Any help, idea or advice would be appreciated!

1 ACCEPTED SOLUTION
bidevsugmen
Resolver I
Resolver I

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.image.png

 

 

Thanks,

Suguna.

 

View solution in original post

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
bidevsugmen
Resolver I
Resolver I

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.image.png

 

 

Thanks,

Suguna.

 

Anonymous
Not applicable

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
Not applicable

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
Not applicable

Greg_Deckler
Super User
Super User

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...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.