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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Special Calendar Table

Dear Good Morning,

I need your support in the following:

Automatically generate the calendar table with dax, this table should have:

  • C_Periodo only from 1 to 60, month 60 will be obtained from an auxiliary table called Period, whose only element will be a date in the format 20210701, and the other months will decrease
  • The calendar table should only show the first days of each month, and this value will decrease until it reaches 1, that is, take 60 months

jminanoc_0-1632244908496.png

Thanks in advance for the support

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

Hi, @Syndicate_Admin 

 

1. to create a measure to get the latest date:

_MaxDate = CALCULATE(MAX('Auxiliar Periodo'[C_periodo]),ALL('Auxiliar Periodo'))

2. to create a calculated table:

Table = 
ADDCOLUMNS(
    GENERATESERIES(1,60,1),
    "date",FORMAT(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]),"d/mm/yyyy"),
    "Mon",FORMAT(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]),"mm"),
    "N_Mon",FORMAT(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]),"mmm"),
    "M-Y",FORMAT(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]),"mmm/yyyy"),
    "year",YEAR(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]))
    )

 

result:

vangzhengmsft_0-1632469421799.png

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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

View solution in original post

4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

Hi, @Syndicate_Admin 

 

1. to create a measure to get the latest date:

_MaxDate = CALCULATE(MAX('Auxiliar Periodo'[C_periodo]),ALL('Auxiliar Periodo'))

2. to create a calculated table:

Table = 
ADDCOLUMNS(
    GENERATESERIES(1,60,1),
    "date",FORMAT(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]),"d/mm/yyyy"),
    "Mon",FORMAT(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]),"mm"),
    "N_Mon",FORMAT(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]),"mmm"),
    "M-Y",FORMAT(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]),"mmm/yyyy"),
    "year",YEAR(EDATE('Auxiliar Periodo'[_MaxDate],-60+[Value]))
    )

 

result:

vangzhengmsft_0-1632469421799.png

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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

Thank you very much, it was exactly what I wanted

amitchandak
Super User
Super User

@Syndicate_Admin , based on what I got so far

new table =

addcolumns(generateseries(0,59,1), "period", format(date(2017,1+[Value],1) , "YYYYMMDD"))

 

other you can add based on that, or keep on adding using the format

Thank you for your prompt response, it gives me a starting point to do what I am doing, since the period 60, is variable every month and that comes from the period table I commented. In the example it tells me that the 60th month has to be July2021, then the 59th is June 2021 and so on until you reach month 1. But the next month the table period will change and it will be 20210801, then the 60th month will be August 2021, the 59th July 2021 and so on. In conclusion my period table is tied to the parameter that comes from the other table.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.