Forum Discussion
AdministratorSpecial 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
Thanks in advance for the 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:
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.
4 Replies
- amitchandak
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
Syndicate_AdminThank 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.
- v-angzheng-msft
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:
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.- Syndicate_Admin
Thank you very much, it was exactly what I wanted
