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))
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!!

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        You are welcome.

  • 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: