Custom sorting month and Total Sales by Custom Period

[bencheuch]

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

[lbendlin]

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

[bencheuch]

Thank you sir, I updated my post 🙂

[lbendlin]

Your sample data is missing transactions for "last year". Please check.