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...
  • 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


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


  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Thank you very much, you helped me a lot.

    Best regards