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
Gjakova
Post Patron
Post Patron

This week price avg. vs last week avg. & this month price avg. vs last month price avg [HOW?!)

I have spent DAYS, I'm serious... on trying to figure this out. I'm new to PBI and what I need is the following thing: it sounds easy, but I guess it is one of the hardest things to do in Power BI?

I have quite some queries, but the two most important queries are let's say: QueryA (contains the info) and QueryB (calendar)

 

Name of the table: PriceInfo --> contains 4 columns and 8000 rows

Key (link to my calendar)ABC
1€ 1.50€ 1.651.70
2€ 1.70€ 1.50€ 1.15
............
8000€ 1.10€ 1.70€ 1.65

 

Name of the table: Calendar --> contains all the date info

Key (link to my calendar)DateWeeknumberWeekdayMonthNameYearDayDate
101/01/200914January20091
202/01/200915January20092
...      
800028/02/202095February202028

 

Since my data is connected to an API which refreshes every week, I need a measure or something that makes sure that my numbers are up-to-date every week. I have tried everything on this forum, but nothing works.

I would like to visualise in a Card the following:

  • Price of A this week: €1.70 (the price on 28/02/2020)
  • Price of B this week: €1.10 (the price on 28/02/2020)
  • Price of C this week: €1.65 (the price on 28/02/2020)

And then some other cards with:

  • Price of A last week: €1.40 (the price on 21/02/2020)
  • Price of B last week: €1.30 (the price on 21/02/2020)
  • Price of C last week: €1.55 (the price on 21/02/2020)

And then do exactly the same, but for months.

 

Could somebody help me out please? I feel like I would also need this type of thing for future projects, so all help is welcome!

2 ACCEPTED SOLUTIONS

@Gjakova I selected Feb 28th as a date. Just 3 measures and easy to maintain, solution attached. Would appreciate Kudos 🙂 if my solution helped.

 

 



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.

View solution in original post

@Gjakova oh ok, not sure why but comma was always there. Anyhow glad it is working. We did unpivot so that with few measures we can work on all the fuel categories, otherwise, we need to write measures for each column (each category) and that would have been 3 categories x 3 columns = 9 measures and also it is not scalable,  and best practice. You cannot even filter if you had 3 columns and now can easily filter. Not sure if this answered your question but if you have any further questions, I can gladly explain.



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.

View solution in original post

32 REPLIES 32

@parry2k here are some extra screenshots for a clarification of what I adjusted to make it work:
solution2.pngsolution1.png

Kudos are welcome if it gave you some clarification 😛

Hi there, it is already late on the other side of the world for screen sharing, but I appreciate the help!

Eventually your measure was correct, the only thing that was missing was a , before MONTH and before DAY.

Solution: ,DAY instead of DAY and ,MONTH instead of MONTH.
Maybe the Power BI settings are different in other regions of the world? Anyhow, thanks a lot for the measurement. Just one more question: why did I had to unipivot all the info except for ID, would it otherwise not have worked? (The thing you mentioned in your 2nd or 3rd post here).

 

Thanks a lot!

@Gjakova oh ok, not sure why but comma was always there. Anyhow glad it is working. We did unpivot so that with few measures we can work on all the fuel categories, otherwise, we need to write measures for each column (each category) and that would have been 3 categories x 3 columns = 9 measures and also it is not scalable,  and best practice. You cannot even filter if you had 3 columns and now can easily filter. Not sure if this answered your question but if you have any further questions, I can gladly explain.



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.

Ah I did not know that! Thanks a lot. So it is better to have more rows than columns? Or is that just the case when you want to create measures?

@Gjakova basic rule:

 

facts on which you do the calculations, usually are long and thin

dimension by which you measure facts, can be wide 

 

if it makes sense. Anyhow, that comma thing is bizarre for me but I'm going to sleep over it for now. We already spent a lot of time on it and I'm glad it is working for you. Cheers!!



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 hi there! I have some question still about the date function.

Question 1: the filter pane that you had created only allows to select one certain date. When I tried to recreate it, I couldn't achieve the same end result. So mine let's users click on multiple days (which leads to question 2), how can I do the same as you did?

 

Question 2: with my old dashboard, I had a line chart with the three gas types in it. Since we unpivoted the gas types, It only allows me to select the overall gas type in a line chart. I could put a filter pane next to it of course or create some buttons, but do you know a workaround to get it all in one?

 

Question 3: on my old dashboard I had a date slicer. So I could see what the average price was of a certain month, 7 years ago. Now when I try to do that, all the gas prices seem summarized.

 

So your solution did help me to create a this week/last week/last month visualisation, but I had to sacrifice my other visualisations.

Is it an option to make 2 data tables? One data table where I have the gas prices as before (not unpivoted, what I had before) and one data table where I have the gas prices unpivoted (your solution)?

Thanks in advance!

@Gjakova let me try to answer:

 

#1 - Go to format pane and change slicer type to single select

image.png

 

#2 - you should put gas type in the legend and it should work

 

#3 - as we did in our graph, put avg in a table visual and put gas type in it, it is more flexible and single measure work across all types, I'm sure surely previously your created 3 measures to achieve a result for each gas type.

 

Hope this help, I'm here to help or answer further questions.



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 hi there!

Question #1 and #2 are solved, although I don't understand your solution regarding my third question...

In the sample file you provided you had two matrix tables and three multi-row cards of my gas types prices for each period. The problem is that I alway have to select a certain date, otherwise it shows the SUM, instead of the measurement. And when I select a date, then you can only see one data point in my line chart. Instead of everything from the last 15+ years.

Thanks for the tip! Yeah it is bizarre, we spent a lot of time on just a , 
Anyways cheers!

@Gjakova did you marked mastercalendar table as date table

 

image.png

 

Would appreciate Kudos 🙂 if my solution helped.



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.

Yes, it was already selected like that. Does it not show the same error at yours? Because I haven't made any changes yet...

2ErrorPBI.png

@parry2k 

Done! Here is the updated version: [link removed]

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.