Forum Discussion
Three years cycle grouped column
i need to create a column in powerbi that will group years
so i want to do a 3 years cycle time
if column year is:
2016,2017,2018 return 2018,
and
2017,2018,2019 return 2019
and
2018,2019,2020 return 2020
and
2019,2020,2021 return 2021
and
2020,2021,2022 return 2022
and
2021,2022,2023 return 2023
and so on
in this data 2022 is missing keep the sequence and treat it as missing data but the sequence
must not change and if year is null return null
then sequence must continue like this ,
make sure that even if the year is included in the first group or the previous group it must be included also on the next group following the sequence
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
One of ways to achieve this is to create a measure with using WINDOW DAX function, and create a recent-three-year-window in the measure.
WINDOW function (DAX) - DAX | Microsoft Learn
expected result measure: = VAR _window = WINDOW ( -2, REL, 0, REL, ALL ( 'calendar'[Year] ), ORDERBY ( 'calendar'[Year], ASC ) ) VAR _condition = COUNTROWS ( _window ) = 3 RETURN IF ( _condition, CALCULATE ( SUM ( sales[sales] ), _window ) )
5 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
One of ways to achieve this is to create a measure with using WINDOW DAX function, and create a recent-three-year-window in the measure.
WINDOW function (DAX) - DAX | Microsoft Learn
expected result measure: = VAR _window = WINDOW ( -2, REL, 0, REL, ALL ( 'calendar'[Year] ), ORDERBY ( 'calendar'[Year], ASC ) ) VAR _condition = COUNTROWS ( _window ) = 3 RETURN IF ( _condition, CALCULATE ( SUM ( sales[sales] ), _window ) ) 
v-kathullacCommunity Support
Hi Rirhandzu ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Regards,Chaithanya.
Syndicate_AdminAdministrator
Thank you the creation of grouped calendar table this help and the solution is working
- v-kathullac
Hi Syndicate_Admin ,
Thank you for sharing your update and confirming that you dont have any issue. i request you to please accept the post as a solution, this will help other community members who might face a similar issue.
Regards,
Chaithanya.
- v-kathullac
Hi @Syndicate_Admin ,
Thank you for sharing your update and confirming that you dont have any issue. i request you to please accept the post as a solution, this will help other community members who might face a similar issue.
Regards,
Chaithanya.