Custom sorting month and Total Sales by Custom Per...

bencheuch · ‎11-17-2022

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