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
Super User
Super User

@Gjakova this can be achieved with few measures, you need to unpivot your data for better design and for scalbility. Will be hard to put together everything here, if you can send sample pbix file (remove sensitive info before sharing), will get you the solution.

 

 



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 

Thanks for your help! I have attached a sample file of the document: 

EDIT: old link removed

Let me know if this is enough for you.

@Gjakova since I cannot make changes to the data in power query, can you do the following:

 

- select ID column on right click, select unpivot other columns , it will add two columns  attribute and value , rename these columns if you want otherwise leave it like this and resend the file.



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.

@Gjakova yes, and also when you say month it means same day last month?



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 Yes, I do mean that. But I'm flexible with it. As long as one card shows the most up-to-date data (in this case 30-03-2020) and when it gets updated --> 06-04-2020. (Thus, this week/last week)

 

And another one shows 28-02-20 or when it gets updated --> 06-03-2020. (Thus, last month).

@Gjakova that's fine, it is a matter of filtering on the datum, or default filter to today's date, since we don't have today's date, I added a result. As a best practice you want to keep few visuals on the report, as each visual run its own query and can have a performance hit, maybe not in this case but just as a good practice. Anyhow, do you think one of the following views would work?

 

image.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 Yes that is fine! I'll transform the table into a card in my own report. Did you create new measurements and are you sure that the prices are correct? I thought that for e.g. Euro95 would be aroun 1.50 +/- this week and around 1.70 +/- last month. But it seems that the differences between the timeframes were not that high.

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

Thank you! It works. Altough I wanted to visualise this as a card, it seems that it is not possible. So I should probably try some other visualisations.

 

This will help me with some other future projects as well, so thank you! 🙂

@Gjakova you can always put these measures on the card and filter each measure by fuel type, it will work, 



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.

@Gjakova this might be better visualization, just 3 visuals compare to KPIs, just my crazy ideas. Would appreciate Kudos 🙂 if my solution helped.

 

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

That looks great! I always made the mistake of filtering on date instead of product type. So I've learned two new things!

 

But would you happen to know why I receive this type of error?

ErrorPBI.png

@Gjakova it is ok on my end, just press enter and see if it works. 



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.

I tried that, but then all my visuals get an error: see example at last week:

3ErrorPBI.png

what options you get after -1, mine looks like this, wondering it is language thing.

 

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

I don't get any options...

4ErrorPBI.png

@Gjakova bizzare, is datum column is date type when you selected Mark as date table  , did you used datum as a column for a date? Can you share the screenshot of mastercalendar table with all the columns showing in field pane, datum icon should look different compare to others, which shows it is marked as a date.



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.

I managed to solve it!

Old format: 

Last Month = CALCULATE ( [This Week], DATEADD( MasterCalendar[Datum], -1, MONTH ) )

New format: 

Last Month = CALCULATE ( [This Week], DATEADD( MasterCalendar[Datum], -1, ,MONTH ) )
 
So I just had to add a , before MONTH and DAY 😥

@Gjakova can we do a screen sharing to take a look it? I'm bit lost.



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.

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.