Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
User | Count |
---|---|
78 | |
74 | |
42 | |
32 | |
28 |
User | Count |
---|---|
100 | |
93 | |
52 | |
50 | |
48 |