Hello everyone,
I am struggling to achieve something using Power Query and Power Pivot and I hope someone can give me some hints to achieve it. I believe I should use some kind of combinaison of FILTER and CALCULATE but I am blocked.
I want to create some Sales reports for my company. In my company, we are working with products catalogues, that occur twice a year. In other words it means that one year of work starts from the 1st of Octobre and ends on the 30th of September.
I would like to achieve 2 things :
- Get the Sales Amount per Month per Customer per Catalogue (also display 0 if no sales were made on that month)
- AND get the Sales Amount from same month from last year
- Get a dedicated measure that makes the Sum of all the months in the catalogue (so from Octobre to Septembre next year)
- AND get the same measure but from the previous catalogue
To create my custom order, I first created a Calendar Table, then LeftJoin a text column having the month in the order I need (1-October, 2-November,... 12-September).
So far in a Power Pivot Table the Custom order by catalogues seems to be working. I can also make every customer appear even if they did not order on a specific month.
However I can not get the Sales_LastYear to work. I usually could make it work by using
=CALCULATE([Sales]; PARALLELPERIOD('CALENDAR'[Date];-12;MONTH))
but it is not working with my Custom Order.
Also regarding the TotalSalesCatalogue I tried creating a calculated column using GroupBy but it is not working, and I know that I should (almost) always use Measure instead of anything else.
In other words this is what I currently have :
This is what the expected output :
EDIT : here is the link download my file from Google Drive : https://drive.google.com/drive/u/1/folders/1JUTHwcUw-peWfbKQPpW2t3jZaPGZmqRM
Thanks in advance,
With regards
