Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
o59393
Post Prodigy
Post Prodigy

Calendar Tables - Dynamic rolling 12M

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:

 

o59393_1-1645399475455.png

 

 

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.

1 ACCEPTED SOLUTION

Hi,

Try this Table formula

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

 

First of all thanks for your support.

 

I tried the formula but the following error came up:

 

o59393_0-1645409623514.png

 

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!

Hi,

Try this Table formula

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

 

It worked the day by day:

o59393_1-1645410613221.png

 

 

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

 

o59393_0-1645410560757.png

 

Can you guide me?


Thanks!

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

 

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

 

Thank you!!

 

 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

o59393_2-1645410703472.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.