Calendar Tables - Dynamic rolling 12M

[o59393]

Hi all

I´d like to have a dynamic calendar which contains the last rolling 12 months. 

For example today is 2022-2-20. Therefore the calendar should go from February 2021 to January 2022. 

I am trying to get something like this, which the day is always the 1st of each month:

If we were 2022-3-5 for example, then the calendar should reflect March 2021 to February 2022, and so on.  

How could I have a rolling 12M calendar?

Appreciate the help commnity.

[Ashish_Mathur]

Hi,

A Calendar table should always have a row for each day from the first to the last day.  So getting only the first day of each month is a bad idea, even if it is possible to do so.  To get a rolling 12 month calendar, you may try this Table formula

=calendar(edate(startofmonth(today()),-12),startofmonth(today())-1)

I have not tried this but am hoping that it will work.

[o59393]

Hi @Ashish_Mathur 

First of all thanks for your support.

I tried the formula but the following error came up:

How can I get it ok?

And also, taking your advise, how would a 12M rolling calendar would look like with a row for each day from the first to the last day?

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

Try this Table formula

Calendar = CALENDAR(edate(EOMONTH(today(),-1)+1,-12),EOMONTH(today(),-1))

[o59393]

Hi @Ashish_Mathur 

It worked the day by day:

I tried to have the first day per month only, but I got this error:

Can you guide me?

Thanks!

[Ashish_Mathur]

Hi,

As i mentioned earlier, it is a bad idea to do that because a Calendar should always have a row for each data from start to finish.  Furthermore, i do not even know how to do that in the DAX language.  You should create the Calendar Table in the Query Editor instead and then filter that table to only show the first of each month.  Someone else will help you with that.

[o59393]

Hi @Ashish_Mathur 

It's ok. Your code solved my problem, I will stick to your recommendation of full calendar instead.

Thank you!!

[Ashish_Mathur]

You are welcome.

[o59393]

@Ashish_Mathur It could be having the extraction of the first date from each month by using this column already created: