Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
MalavikaUB
New Member

Rolling 13 periods without a structured date column

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

 

FiscalYearFiscalPeriodNetRevenue
202112311
202125174
202133206
202144284
202151968
202165140
202173486
202185046
202194120
2021102952
2021111828
2021121501
2021134751
202215151
202224070
202231700
202241680
202252159
202265018
202274645
202283984
202294997
2022103791
2022112338
2022124855
2022132645
202312114
202322618
202335487
202345056
202355004
202365298
202373355
202384055
202394089
2023102132
2023113143
2023125226
2023133087
202412699
202421938
202433476
202444150
202453454
202461545
202472104
202484530
202494305
2024101266
2024112330
2024123335
2024134098
1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

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()
)

vjunyantmsft_0-1702879043371.png

The results are shown below:

vjunyantmsft_1-1702879073163.png

 

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.

View solution in original post

3 REPLIES 3
v-junyant-msft
Community Support
Community Support

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()
)

vjunyantmsft_0-1702879043371.png

The results are shown below:

vjunyantmsft_1-1702879073163.png

 

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
Super User
Super User

@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

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?

MalavikaUB_0-1702568241277.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.