Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
For a report we want to show qty's per month in a graph. We have quantities on Y-axis, months on X-axis and each line is a year.
Now we want to show the change in quantities in percentage from the month before. Although I think I have a problem with the data.
I made example data, these are the actual columns I have. As you can see not every month has a quantity. This means when I just do a month - 1 formula I wont get all data because there is a possibility that product 202 has quantities in february but not in march so the formula won't find the data.
So I think I had to make a new table with a possible combinations of customer, product, year and month and then do a lookup for the quantities. If there is no quantity then show 0.
The file is quite big (~400.000 lines of data), when I tried to make all possible combinations powerbi stops working. Do you guys know some way to do this in powerbi or do I need to work with other programs to create the data I need (if it is needed)? Or is my computer just to slow.
Customer | Product | Year | Month | Qty |
DE1 | 202 | 2018 | 1 | 10 |
DE1 | 202 | 2018 | 2 | 4 |
DE1 | 202 | 2018 | 5 | 8 |
DE1 | 202 | 2018 | 6 | 6 |
DE1 | 202 | 2018 | 10 | 7 |
DE1 | 202 | 2018 | 11 | 8 |
DE1 | 202 | 2018 | 12 | 6 |
DE1 | 202 | 2019 | 1 | 5 |
DE1 | 202 | 2019 | 2 | 10 |
DE1 | 202 | 2019 | 3 | 12 |
DE1 | 202 | 2019 | 4 | 11 |
DE1 | 202 | 2019 | 5 | 6 |
DE1 | 202 | 2019 | 6 | 8 |
DE1 | 202 | 2019 | 7 | 7 |
DE1 | 202 | 2019 | 8 | 10 |
DE1 | 202 | 2019 | 9 | 1 |
DE1 | 202 | 2019 | 10 | 2 |
DE1 | 202 | 2019 | 11 | 2 |
DE1 | 202 | 2019 | 12 | 10 |
DE1 | 203 | 2018 | 1 | 10 |
DE1 | 203 | 2018 | 2 | 12 |
DE1 | 203 | 2018 | 3 | 11 |
DE1 | 203 | 2018 | 4 | 11 |
DE1 | 203 | 2018 | 7 | 10 |
DE1 | 203 | 2018 | 8 | 9 |
DE1 | 203 | 2018 | 9 | 8 |
DE1 | 203 | 2018 | 11 | 7 |
DE1 | 203 | 2018 | 12 | 10 |
DE1 | 203 | 2019 | 1 | 12 |
DE1 | 203 | 2019 | 2 | 11 |
DE1 | 203 | 2019 | 5 | 1 |
DE1 | 203 | 2019 | 6 | 10 |
DE1 | 203 | 2019 | 8 | 12 |
DE1 | 203 | 2019 | 9 | 1 |
DE1 | 203 | 2019 | 12 | 5 |
Solved! Go to Solution.
Hi, @AlexJB
Import mode is not recommended if the data is very large. You can consider import the data into a database so that it can be accessed by using directquery.
Then you need a calendar table with consecutive dates and create measures based on this calendar table to calculate the result.
Best Regards,
Community Support Team _ Eason
Hi, @AlexJB
Import mode is not recommended if the data is very large. You can consider import the data into a database so that it can be accessed by using directquery.
Then you need a calendar table with consecutive dates and create measures based on this calendar table to calculate the result.
Best Regards,
Community Support Team _ Eason
I indeed tought about this aswell. Although I wanted to see if there is any other way to do this. Thanks for the answer.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
91 | |
81 | |
65 | |
65 | |
60 |
User | Count |
---|---|
170 | |
114 | |
102 | |
73 | |
69 |