Help to create a Measure to group data by a fixed number of consecutive Rows

[BenW86]

Hi everyone,

I'm looking to create a Measure that allows me to group sales data by a fixed number of consecutive Rows - to calculate the sum of 12 months sales periods for a Product. For example, if a Product goes on sale in the middle of the year, and the first period of sales is 201706 (as in graph below), how do I calculate the Sum of Volume for the next 12 periods to make up the total 12 month sales period, and then the following 12 month periods?

My aim is to have a graph that shows me:

Y1: Total sales (Sum of Volume for 13 consecutive periods)

Y2: Total sales (Sum of Volume for 13 consecutive periods)

Y3: Total sales (Sum of Volume for 13 consecutive periods)

Etc!

[tamerj1 · Accepted solution]

Hi @BenW86 
Please refer to attached sample file with the proposed solution

12 Months Period = 
"Y" & QUOTIENT ( RANKX ( 'Table', 'Table'[Period],, ASC, Dense ), 12 ) + 1

[BenW86]

This works perfectly, thank you @tamerj1! 

I wonder if you might help with another issue...? I've just noticed that no data was recorded for certain periods (notably during the pandemic where accurate sales data could not be guaranteed). Is there a way to account for this?

For example: Here you can see that Periods 4-6 are missing from the data set:

Would I need to build a proper calendar to ensure Periods are still counted within the Year (Y), even if they are not present? 

Thanks again!

[tamerj1]

@BenW86 
This need to be done in the date table

12 Months Period = 
"Y" & QUOTIENT ( 
        RANKX ( 
            'Table', 
            LEFT ( 'Table'[Period], 4 ) * 12 + RIGHT ( 'Table'[Period], 2 ),, 
            ASC, Skip 
        ), 
        12 
    ) + 1

or 

12 Months Period = 
"Y" & QUOTIENT ( 
        RANKX ( 
            'Table', 
            'Table'[Year] * 12 + 'Table'[Month],, 
            ASC, Skip 
        ), 
        12 
    ) + 1