Forum Discussion

MalavikaUB's avatar
MalavikaUB
New Member
2 years ago
Solved

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
General Comment
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      MalavikaUB
      New Member
      2 years ago

      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?

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.