Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

List a Calendar Table

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's avatar
    Hariharan_R
    4 years ago

    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


2 Replies

  • Hariharan_R's avatar
    Hariharan_R
    Solution Sage
    4 years ago

    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