cancel
Showing results for 
Search instead for 
Did you mean: 
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.

View solution in original post

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!