Change in percentage from month before

[AlexJB]

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

[v-easonf-msft · Accepted 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

[AlexJB]

I indeed tought about this aswell. Although I wanted to see if there is any other way to do this. Thanks for the answer.