List a Calendar Table

[Syndicate_Admin]

Dear I have a calendar table that is dynamic, ranging from a minimum date (1/1/2020) to a maximum date (30/6/2022), for example I put it in hard.

What I want is to add a correlative to the beginning "Nro", in such a way that all the dates of the last month i.e. June 2022, let be 1, all the dates of the month of May 2022 would be 2, all the dates of April 2022 would be 3 and so on.

The table for the calendar I'm using is:

Thanks in advance for your help

Calendar =

WHERE Date = CALENDAR(

DATE( 2020, 1, 1) ,

DATE (2022, 6, 30)

)

return SELECTCOLUMNS

(

Date,

"Date", [Date],

"Year" , YEAR ( [Date] ),

"My", UPPER ( FORMAT ( [Date], "MMM") ),

"MesNro", ( FORMAT ([Date], "MM" ) ),

"Day", ( FORMAT( [Date], "DD" ) ),

"QuarterNro", ROUNDUP(MONTH([Date]) / 3, 0 ),

"Quarter" , "Q" & ROUNDUP( MONTH( [Date] ) / 3, 0 ),

"AñoMes", YEAR ( [Date] ) * 100 + MONTH ([Date]),

"MesAño", UPPER(FORMAT ([Date], "MMM" ) ) & "-" & RIGHT (YEAR ( [Date] ),4),

"AñoQ", YEAR ( [Date]) * 100 + ROUNDUP( MONTH( [Date] ) / 3, 0 ),

"QAño" , "Q" & ROUNDUP( MONTH( [Date] ) / 3, 0 ) & "- " & RIGHT (YEAR ( [Date] ),4)

)

[Hariharan_R · Accepted solution]

Hi @Syndicate_Admin 

Follow the below steps.

1. Create a new DAX table using below script.

Rank = 
VAR _Date = CALENDAR(
DATE( 2020, 1, 1) ,
DATE (2022, 6, 30)
)
return DISTINCT(SELECTCOLUMNS
(
_Date,

"MesAño", UPPER(FORMAT ([Date], "MMM" ) ) & "-" & RIGHT (YEAR ( [Date] ),4),
"AñoMes", YEAR ( [Date] ) * 100 + MONTH ([Date])
))

2. Add calculated column on the Rank table

Nro = RANKX('Rank','Rank'[AñoMes],,DESC)

3. Create a relationship

4. Create a calculated column in your date table 

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin

[Syndicate_Admin]

Thank you very much, you helped me a lot.

Best regards