How to create Rolling 6 month with YYYYM column ?

[itsmebvk]

Hi Folks, I need some help in crating DAX for rolling 6 months without date column.

My fiscal year starts on 1st Sunday of every August, I dont have date column in the tables but I have YYYYP column available in the both Date and Category table. Using YYYYP can we create Rolling 6 months measure?

Note: I tried to use DATEYTD, TOTALYTD but I am unable to get desired output as my Fiscal year start date is not constant in starts on 1st Sunday of every August.

CAn you please suggest anywork around to get rolling 6 months data?

Thanks in adavance.

Fiscal Year	Fiscal Month	YYYYM	Revenue
2020	1	20201	100
2020	2	20202	200
2020	3	20203	300
2020	4	20204	400
2020	5	20205	500
2020	6	20206	600
2020	7	20207	700
2020	8	20208	800
2020	9	20209	900
2020	10	20210	1000
2020	11	20211	1100
2020	12	20212	1200
2020	13	20213	1300
2021	1	20211	1400
2021	2	20212	1500
2021	3	20213	1600
2021	4	20214	1700
2021	5	20215	1800

Category Table

Category	Revenue	Fiscal	Fiscal Month	YYYYM
A	1200	2020	12	20212
A	1300	2020	13	20213
A	1400	2021	1	20211
A	1500	2021	2	20212
A	1600	2021	3	20213
A	1700	2021	4	20214
A	1800	2021	5	20215
B	1000	2020	12	20212
B	1100	2020	13	20213
B	1200	2021	1	20211
B	1700	2021	2	20212
B	1800	2021	3	20213
B	`1400	2021	4	20214
B	2000	2021	5	20215

[v-alq-msft · Accepted solution]

Hi, @itsmebvk 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Category:

You may create measures as below.

6 months roll total = 
CALCULATE(
    SUM('Table'[Revenue]),
    FILTER(
        ALL('Table'),
        'Table'[YearMonth] in 
        TOPN(
            6,
            CALCULATETABLE(
                DISTINCT('Table'[YearMonth]),
                FILTER(
                   ALL('Table'),
                   'Table'[YearMonth]<=SELECTEDVALUE('Table'[YearMonth])
                )
            ),
            [YearMonth]
        )
    )
)

6 months roll total category = 
CALCULATE(
    SUM('Category'[Revenue]),
    FILTER(
        ALL('Category'),
        Category[Category]=SELECTEDVALUE(Category[Category])&&
        'Category'[YearMonth] in 
        TOPN(
            6,
            CALCULATETABLE(
                DISTINCT('Category'[YearMonth]),
                FILTER(
                   ALL('Category'),
                   Category[Category]=SELECTEDVALUE(Category[Category])&&
                   'Category'[YearMonth]<=SELECTEDVALUE('Category'[YearMonth])
                )
            ),
            [YearMonth]
        )
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@itsmebvk , Create a column like . but have a separate period table

Fiscal Month year =[Fiscal Year]*100 + [Fiscal Month]

Create a rank on this and do analysis using that. Refer to this week's article. Once you rank it will work n same manner

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Last 6 period Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[period Rank]>=min('Date'[period Rank])-6 && 'Date'[period Rank]<=max('Date'[period Rank])))

here Date will be your period table

[itsmebvk]

@amitchandak 

Thanks Amit for quick reply. I have an issue here, my date table has dates until 2025 and using "Fiscal Period Start Date" and "Fiscal Period End Date" I am able to identify current period I am in. Now using that how we will get rolling 6 months from that particular current period.

[PaulDBrown]

@itsmebvk 

The problem with using a YYYYMM column for rolling periods is that the values by definition are not consecutive. For example:

Year	Month	YYYYMM
2019	11	201911
2019	12	201912
2020	01	202001
2020	02	202002
2020	03	202003

so when you want to calculate the rolling 6 months, if you use the YYYYMM value and filter by subtracting 5 months (to have a 6 month total including the current) you might get a period not included. For example:

for the month of March 2020, you would get 202003 - 5 = 201998, which is not a valid YYYYMM value. (Subtracting 5 to get the 6 months including the current)

The easiest way to get around this arguably (as @amitchandak has suggested) is to include an index column for your YYYYMM column in ascending order, and use that in calculations. For example:

YYYYMM	YYYYMM Index
201910	1
201911	2
201912	3
202001	4
202002	5
202003	6

To get the rolling average for the last 6 months, you can now subtract 5 from you YYYYMM Index to use in the filter statements in your measures etc...

BTW, if you want to create a YYYYMM index using RANK, you will need the values to be in the format YYYYMM. Otherwise the rank  will not be what you expect: 201912 is a higher value than 20201 (for YYYYM)