how to create a Rolling Calendar of 6 Years with the interval of 2 months?

[jayasurya_prud]

I am working on a report where I am trying to create a rolling calender in 2 ways.

1. As a different table

2. As a column in an existing table

The rolling calender is for 6 years with the interval of 2 months

ex:

03/01/2022  

05/01/2022

07/01/2022

09/01/2022

How to execute this?

[v-yangliu-msft · Accepted solution]

Hi  @jayasurya_prud ,

You can change the date of Dax inside to 3/1/2022 and "12/31/2028"

MonthTable =
var FullCalendar =
ADDCOLUMNS(
CALENDAR("2022/3/1","2028/12/31"),"Month Number",MONTH([Date]),
"Year",YEAR([Date]),
"Year-Month",VALUE(LEFT(FORMAT([Date],"yyyyMMdd"),6)),
"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",
YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
var _table1=
SUMMARIZE(FullCalendar,[Year-Month])
var _table2=
FILTER(
    _table1,
   MOD( ([Year-Month] - MINX(_table1,[Year-Month])),2) =0)
var _table3=
ADDCOLUMNS(
    _table2,"Date",DATE(LEFT([Year-Month],4),RIGHT([Year-Month],2),1))
return
SUMMARIZE(_table3,[Date])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Adamboer]

To create a rolling calendar in two different ways, you can follow the steps below:

1. As a different table:

• Click on the "Modeling" tab in the ribbon menu and then select "New Table".
• In the formula bar, enter the following formula:
  RollingCalendar = CALENDAR(DATE(2022, 3, 1), DATE(2028, 2, 1))
  
  • This formula will create a new table called "RollingCalendar" with a range of dates from March 1, 2022, to February 1, 2028, with an interval of two months.
  1. As a column in an existing table:
  • Select the existing table in which you want to add the rolling calendar column.
  • Click on the "Modeling" tab in the ribbon menu and then select "New Column".
  • In the formula bar, enter the following formula:
    
    RollingCalendar = CALENDAR(DATE(2022, 3, 1), DATE(2028, 2, 1), "MONTH")
    
    
    • This formula will create a new column called "RollingCalendar" in the selected table with a range of dates from March 1, 2022, to February 1, 2028, with an interval of two months.

    Once you have created the rolling calendar, you can use it in your report by selecting it as a data source or by adding it to your existing tables using a relationship.

    
    
    
    
  
  
  
  

[v-yangliu-msft]

Hi  @jayasurya_prud ,

Here are the steps you can follow：

1. Create calculated table.

MonthTable =
var FullCalendar =
ADDCOLUMNS(
CALENDAR("2022/3/1","2022/12/31"),"Month Number",MONTH([Date]),
"Year",YEAR([Date]),
"Year-Month",VALUE(LEFT(FORMAT([Date],"yyyyMMdd"),6)),
"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",
YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
var _table1=
SUMMARIZE(FullCalendar,[Year-Month])
var _table2=
FILTER(
    _table1,
   MOD( ([Year-Month] - MINX(_table1,[Year-Month])),2) =0)
var _table3=
ADDCOLUMNS(
    _table2,"Date",DATE(LEFT([Year-Month],4),RIGHT([Year-Month],2),1))
return
SUMMARIZE(_table3,[Date])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[jayasurya_prud]

Hi Liu Yang!

thanks for taking time and helping me out. The solution partially helps. 

I want the same for 6 years like 2022 to 2028

how to get that?