Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
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
Thank you very much, you helped me a lot.
Best regards
User | Count |
---|---|
86 | |
82 | |
68 | |
66 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |