Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Dear Good Morning,
I need your support in the following:
Automatically generate the calendar table with dax, this table should have:
Thanks in advance for the support
Solved! Go to Solution.
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.
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.
Thank you very much, it was exactly what I wanted
@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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |