Rolling 13 periods without a structured date column

[MalavikaUB]

Hi, I would need help with rolling 13 period. 

I have these columns from the table - FiscalYear and FiscalPeriod, Revenue. And not a date column. The requirement is to show the Revenue for the Rolling 13 periods.

I also have parameter - CurrentYear and CurrentPeriod which we use in certain visuals. (preferably as a slicer)

values in :-

CurrentYear

2021

2022

2023

2024

CurrentPeriod
1
2
3
4
5
6
7
8
9
10
11
12
13

FiscalYear	FiscalPeriod	NetRevenue
2021	1	2311
2021	2	5174
2021	3	3206
2021	4	4284
2021	5	1968
2021	6	5140
2021	7	3486
2021	8	5046
2021	9	4120
2021	10	2952
2021	11	1828
2021	12	1501
2021	13	4751
2022	1	5151
2022	2	4070
2022	3	1700
2022	4	1680
2022	5	2159
2022	6	5018
2022	7	4645
2022	8	3984
2022	9	4997
2022	10	3791
2022	11	2338
2022	12	4855
2022	13	2645
2023	1	2114
2023	2	2618
2023	3	5487
2023	4	5056
2023	5	5004
2023	6	5298
2023	7	3355
2023	8	4055
2023	9	4089
2023	10	2132
2023	11	3143
2023	12	5226
2023	13	3087
2024	1	2699
2024	2	1938
2024	3	3476
2024	4	4150
2024	5	3454
2024	6	1545
2024	7	2104
2024	8	4530
2024	9	4305
2024	10	1266
2024	11	2330
2024	12	3335
2024	13	4098

[v-junyant-msft · Accepted solution]

Hi @MalavikaUB ,

Please try this way:
Use this DAX to create a new measure:

SelectedFlag = 
VAR Current_Year = SELECTEDVALUE(CurrentYear[CurrentYear])
VAR Current_Period = SELECTEDVALUE(CurrentPeriod[CurrentPeriod])
VAR CurrentPeriodNumber = (Current_Year * 100) + Current_Period
VAR MinPeriodNumber = CurrentPeriodNumber - 100
RETURN
IF(
    NOT ISBLANK(Current_Year) && NOT ISBLANK(Current_Period),
    IF(
        (SELECTEDVALUE('Table'[FiscalYear]) * 100 + SELECTEDVALUE('Table'[FiscalPeriod])) > MinPeriodNumber && 
        (SELECTEDVALUE('Table'[FiscalYear]) * 100 + SELECTEDVALUE('Table'[FiscalPeriod])) <= CurrentPeriodNumber,
        1,
        0
    ),
    BLANK()
)

The results are shown below:

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

[amitchandak]

@MalavikaUB , Create one table with FiscalYear Fiscal Period , say Period

Create new column

Year Period =  [FiscalYear]*100 + [Fiscal Period]

Create a Rank column

Period Rank = RANKX(Period,Period[year period],,ASC,Dense)

Then you can measure like

Last 13 Period = CALCULATE(sum('Table'[NetRevenue]), FILTER(ALL(Period),

Period[Period Rank]<=max(Period[Period Rank]) && Period[Period Rank]>=max(Period[Period Rank])-13))

Or you can use window function
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[MalavikaUB]

Thanks for the response. 

I did try the solution using RANKX function to fetch only the last 13 months of data in the visual. However, it does not really go well. Could you please assist me?