Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
Solved! Go to 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.
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.
@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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
76 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |