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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mlsx4
Super User
Super User

Difference among years

Hi all!

 

I'm trying to compare my supplies among years. I have created three measures:

 

 

 

Total consumption= SUM(Fact_Invoices[Consumption])
LY_Total consumption = CALCULATE([Total consumption], SAMEPERIODLASTYEAR(Dim_Dates[Date])) 
Diff YoY = [Total consumption]-[LY_Total consumption]

 

 

If no filter is applied, it works fine. But if I want to check all months along the different years it fails. For instance, if I put "July" in slicer, then the table / bar chart only works for 2022 and 2023. I need also to compare 2022 with 2021, 2021 with 2020, and so on...

 

How can I achieve that?

Thank you in advance

1 ACCEPTED SOLUTION

Finally, I have created a calculated column with RELATED to bring names and make it works. It's not an optimum solution but it is what's working... 😅

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@mlsx4 I tried to mimic what you have and it is working;

 

parry2k_0-1695833937764.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@mlsx4 so you are purely filtering on month, not on year?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k 

 

Yes I'm sure. There are no filters in my report except month

The only difference between your "mimic" report and mine is that I have also field center in the rows (Center and then consumption type). The problem is there for sure, because if I ommit the field it works.

 

P.S. I have been doing some tests. Center is a dimTable with the code and the name of the center. If I choose the value from there it doesn't work, but if I put the code taken by supplies table it works. However, I need the name. Relationship is 1 to many on the side of fact table. I don't really know what's happening. And what's more, if I reply every step in a new report, it works fine. The problem is that I cannot separate it from the big report.

 

It is really frustrating!!

Finally, I have created a calculated column with RELATED to bring names and make it works. It's not an optimum solution but it is what's working... 😅

parry2k
Super User
Super User

@mlsx4 not sure why but it should work. What column do you have on the x-axis?  



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k 

 

If I use a matrix, I have:

  • In the rows: name of the center, type of consumption (electricity, water...). 
  • In the columns: hierarchy (year, month)- I have also tried to put it separately with same result
  • In the values: the three measures

And then, as slicers: year, month, center or type of consumption.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors