cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

 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
1 ACCEPTED SOLUTION
Community Support

Hi @MalavikaUB ,

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 3
Community Support

Hi @MalavikaUB ,

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.

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

New Member

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?

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors