Calculation of annual sales for different products with different registration periods

[Syndicate_Admin]

Dear, along with greeting I would like to make an inquiry regarding sales depending on the dates:

I have the following formulas for calculating annual mobile sales, so I like to use DATESISPERIOD because it calculates annual mobile sales (MAT):

Valor USD MAT N = CALCULATE(

[Valor USD],

DATESINPERIOD(TOTAL[Date], .MAX(TOTAL[Date]), -12, MONTH))

Valor USD MAT N-1 = CALCULATE(

[Valor USD],

DATESINPERIOD(TOTAL[Date], .MAX(TOTAL[Date]), -24, MONTH))-CALCULATE(

[Valor USD],

DATESINPERIOD(TOTAL[Date], .MAX(TOTAL[Date]), -12, MONTH))

On the basis I am working, sales are until June 2023, but there are exceptional cases where the sale for a certain product reaches April 2022, which makes the annual mobile year appear "run" from May 2021 to April 2022, when the ideal is January July 2022 to June 2023 according to the formula.

There will be a way to make the above calculation, but taking into consideration that all products have the last registration date June 2023.

Another option I was thinking about is that if there is a way to fill in missing periods in the Base but adding value 0 in the numerical column (the one I will use to do the calculations), in this way, all the products within the base, will have the same number of periods with or without sales.

Thank you very much in advance.

Best regards!

[Syndicate_Admin]

Excellent, I have followed the instructions to the letter, and it has worked correctly.

Thank you!

Best regards

[AmiraBedh · Accepted solution]

Your request has two parts:

1. Modify the moving annual total (MAT) calculation so it considers all products up to June 2023, irrespective of their last registration date.
2. Populate missing periods with 0 values for sales.

- Modifying the MAT Calculation

One way to handle this is to fix the end date for MAT calculation instead of relying on the maximum date of a product's sales. In your case, this would be June 2023.

EndOfMonthDate = DATE(2023,6,30)

Valor USD MAT N = CALCULATE(
[Valor USD],
DATESINPERIOD(TOTAL[Date], [EndOfMonthDate], -12, MONTH)
)

Valor USD MAT N-1 = CALCULATE(
[Valor USD],
DATESINPERIOD(TOTAL[Date], [EndOfMonthDate], -24, MONTH)
) - CALCULATE(
[Valor USD],
DATESINPERIOD(TOTAL[Date], [EndOfMonthDate], -12, MONTH)
)

- Populating Missing Periods with 0

There are different ways to accomplish this, but one common method is using a combination of a Date table and outer join. This table has a single column containing every date you need, from the start of your data to June 2023.

DateTable = CALENDAR(DATE(2021,1,1), DATE(2023,6,30))

Then join this Date Table with your Sales Table. This can be a one-to-one relationship using the date column.

 Whenever you calculate your measures, you'll be using the full range of dates. Where there's no sales data for a given date, the value would be 0.

If your dataset is large, it might be better to consider alternative strategies to fill in missing periods, such as using Power Query in Power BI to generate the missing dates with zero sales.