Forum Discussion

o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

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.   ...
Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    Try this Table formula

    Calendar = CALENDAR(edate(EOMONTH(today(),-1)+1,-12),EOMONTH(today(),-1))
o59393's avatar
o59393
Icon for Post Prodigy rankPost Prodigy
4 years ago

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's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

Hi,

Try this Table formula

Calendar = CALENDAR(edate(EOMONTH(today(),-1)+1,-12),EOMONTH(today(),-1))
  • o59393's avatar
    o59393
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

    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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        o59393
        Icon for Post Prodigy rankPost Prodigy
        4 years ago

        Hi Ashish_Mathur 

         

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

         

        Thank you!!

         

         

    • o59393's avatar
      o59393
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

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