Calculate the previous year cycle sales for a custom period

[Konrad_Schargel]

Hi all, 

I am struggeling with a DAX calculation. 

The situation is the following: 

I would need to calculate the sales growth of my products. The difficult part is that I need to base the calculation on custom cycles.

The cycles are for each three months and I cannot find the way to calculate the sales for the same cycle but in last year. 

Example: 

We are in IE_2024_Q3 and in order to calculate the growth I would need to get the sales for the cycle IE_2023_Q3. 

I have Product table, Period table and Market table and the first two tables are connected to Market table with the ID columns. 

The sales are by month and also I have a calendar table (with no relation with period table) but I think is not necessary to solve this problem because we are working with cycles. 

Using the link you could find the excel file with the three tables. 

Excel file 

The expected result should be the screenshorted below. 

Thank you all 

Regards 

[_AAndrade · Accepted solution]

Hi @Konrad_Schargel,

I recommend you to use a calendar table with quater column and after make a relationship betwenn calendar and your data table.
After that you could use a TotalQtd DAX expression. https://dax.guide/totalqtd/

If you want to use your model I'm attaching a pbix file with my solution.

The final output was this:

[_AAndrade · Accepted solution]

Hi @Konrad_Schargel,

I recommend you to use a calendar table with quater column and after make a relationship betwenn calendar and your data table.
After that you could use a TotalQtd DAX expression. https://dax.guide/totalqtd/

If you want to use your model I'm attaching a pbix file with my solution.

The final output was this:

[Konrad_Schargel]

Hi _AAndrade, 

Thank you very much for your help. 

For the especific country the problem is solved! 

Anyway I would like to ask for other complex situation related with this same topic. 

When I have to work with data for more than one countries they have diferent period name structure. 

And also for Poland, the cycle have 4 months while for Ireland had 3 and we have differents countries the measure that you attached doesn't work.

Do you know how is possible to solve it ?

Thank you! 

Regards

[_AAndrade · Accepted solution]

As I said before probably a calendar table with this kind of periods would help a lot and decrease the complexity of your dax formulas.
Any way, if you prefer using my dax formula I will try to use one Switch funtion to set the previous period acording of the country. But in this case you need to have a country column on your model.
For example:
SWITCH(
   SELECTVALUE(Country),
   "Poland", [Measure to this country],
    "Ireland, [Measure to this country],
    [My first dax measure]
)

[ThomasWeppler]

Hi @Konrad_Schargel 
First I would add quater to the calender table.

Then I would add a relationship between the sales table and the calender table. (one-to-many where the one is to the calender table.

You can now use calculate and use your quanter as a filter.

When you have your measure for this year you can use sameperiodelastyear 
https://learn.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

To get your final result.

I hope this solved your problem 🙂