Rolling 36 Months Calendar per Person

[third_hicana]

Hi Power BI Experts. Asking for your help regarding my project. I want to build a rolling calendar per employee so that I will not create it manually in my data source. So here are the rules or the requirements and my desire outcome.

Rules:

1. Create a monthly rolling calendar (default is 36 months).
2. Each employee should have a rolling 36 months calendar (i.e when the current month ends, it should add another month in the last row.
Example: Employee A has a 36 months calendar starting Jan 2022. If today is Jan 31, 2022 which is end of Jan 2022, it will add row for Jan 2025 to Employee A.
3.  The rolling 36 months calendar in #2 should also simultaneously operate/apply for Employee B, C and D

In my data model I have a list of employees. So after building this rolling calendar, I will create a relationship beween the calendar and employee list with their details.

Please letme know if I need to do something on my end first in order to execute the rules above or better way to build it. I highly appreciate your help. 

Here's my sample data: 

Month	Resource Name
01/01/2022	Employee A
01/02/2022	Employee A
01/03/2022	Employee A
01/04/2022	Employee A
01/05/2022	Employee A
01/06/2022	Employee A
01/07/2022	Employee A
01/08/2022	Employee A
01/09/2022	Employee A
01/10/2022	Employee A
01/11/2022	Employee A
01/12/2022	Employee A
01/01/2023	Employee A
01/02/2023	Employee A
01/03/2023	Employee A
01/04/2023	Employee A
01/05/2023	Employee A
01/06/2023	Employee A
01/07/2023	Employee A
01/08/2023	Employee A
01/09/2023	Employee A
01/10/2023	Employee A
01/11/2023	Employee A
01/12/2023	Employee A
01/01/2024	Employee A
01/02/2024	Employee A
01/03/2024	Employee A
01/04/2024	Employee A
01/05/2024	Employee A
01/06/2024	Employee A
01/07/2024	Employee A
01/08/2024	Employee A
01/09/2024	Employee A
01/10/2024	Employee A
01/11/2024	Employee A
01/12/2024	Employee A
01/01/2022	Employee C
01/02/2022	Employee C
01/03/2022	Employee C
01/04/2022	Employee C
01/05/2022	Employee C
01/06/2022	Employee C
01/07/2022	Employee C
01/08/2022	Employee C
01/09/2022	Employee C
01/10/2022	Employee C
01/11/2022	Employee C
01/12/2022	Employee C
01/01/2023	Employee C
01/02/2023	Employee C
01/03/2023	Employee C
01/04/2023	Employee C
01/05/2023	Employee C
01/06/2023	Employee C
01/07/2023	Employee C
01/08/2023	Employee C
01/09/2023	Employee C
01/10/2023	Employee C
01/11/2023	Employee C
01/12/2023	Employee C
01/01/2024	Employee C
01/02/2024	Employee C
01/03/2024	Employee C
01/04/2024	Employee C
01/05/2024	Employee C
01/06/2024	Employee C
01/07/2024	Employee C
01/08/2024	Employee C
01/09/2024	Employee C
01/10/2024	Employee C
01/11/2024	Employee C
01/12/2024	Employee C
01/01/2022	Employee D
01/02/2022	Employee D
01/03/2022	Employee D
01/04/2022	Employee D
01/05/2022	Employee D
01/06/2022	Employee D
01/07/2022	Employee D
01/08/2022	Employee D
01/09/2022	Employee D
01/10/2022	Employee D
01/11/2022	Employee D
01/12/2022	Employee D
01/01/2023	Employee D
01/02/2023	Employee D
01/03/2023	Employee D
01/04/2023	Employee D
01/05/2023	Employee D
01/06/2023	Employee D
01/07/2023	Employee D
01/08/2023	Employee D
01/09/2023	Employee D
01/10/2023	Employee D
01/11/2023	Employee D
01/12/2023	Employee D
01/01/2024	Employee D
01/02/2024	Employee D
01/03/2024	Employee D
01/04/2024	Employee D
01/05/2024	Employee D
01/06/2024	Employee D
01/07/2024	Employee D
01/08/2024	Employee D
01/09/2024	Employee D
01/10/2024	Employee D
01/11/2024	Employee D
01/12/2024	Employee D
01/01/2022	Employee B
01/02/2022	Employee B
01/03/2022	Employee B
01/04/2022	Employee B
01/05/2022	Employee B
01/06/2022	Employee B
01/07/2022	Employee B
01/08/2022	Employee B
01/09/2022	Employee B
01/10/2022	Employee B
01/11/2022	Employee B
01/12/2022	Employee B
01/01/2023	Employee B
01/02/2023	Employee B
01/03/2023	Employee B
01/04/2023	Employee B
01/05/2023	Employee B
01/06/2023	Employee B
01/07/2023	Employee B
01/08/2023	Employee B
01/09/2023	Employee B
01/10/2023	Employee B
01/11/2023	Employee B
01/12/2023	Employee B
01/01/2024	Employee B
01/02/2024	Employee B
01/03/2024	Employee B
01/04/2024	Employee B
01/05/2024	Employee B
01/06/2024	Employee B
01/07/2024	Employee B
01/08/2024	Employee B
01/09/2024	Employee B
01/10/2024	Employee B
01/11/2024	Employee B
01/12/2024	Employee B

[v-yalanwu-msft · Accepted solution]

Hi, @third_hicana ;

You could create a new table by dax.

Table 2 = 
SUMMARIZE(
UNION( 
SUMMARIZE('Table',[Resource Name], 'Table'[Month],"month2",DATE(YEAR([Month]),MONTH([Month]),DAY([Month]))),
SUMMARIZE('Table',[Resource Name], 'Table'[Month],"month2",DATE(YEAR([Month])+1,MONTH([Month]),DAY([Month]))),
SUMMARIZE('Table',[Resource Name], 'Table'[Month],"month2",DATE(YEAR([Month])+2,MONTH([Month]),DAY([Month]))))
,[Resource Name],[month2])

The final show:

original table.

The final table.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.