Forum Discussion
Create Index Column Based on Month Year Column
Hi everyone
I am new to DAX and I need some help. Please help me solve my problem.
I have my calendar table, which includes a Month Year column. I want to create an Index column base on the Month Year column, but I need help to do so. Please help me. My expected Index should be like this.
Note: I have tried the RANK.EQ function, but my calendar table contains a date level, so it does not return the expected result.
Month Year Index
202001 1
202002 2
202003 3
202004 4
....
202101 13
202102 14
Hi Chinh_Dac ,
Please check the formula:
Column = RANKX('calendar',YEAR('calendar'[Date])*100+MONTH('calendar'[Date]),,ASC,Dense)
9 Replies
Hi Chinh_Dac
Please use the following approach
exemplary baset table:
select your date table and go to
Add Column -> Index Column -> from 1
Resul
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi Mikelytics
Thank you for your quick response. My calendar table is calculated, so it does not appear in the Power Query. I have tried your method on my fact table, but it contains a date level, so it does not give me the expected result.Hi Chinh_Dac
Then feel free to use the following Query for Power Query:
let Source = List.Dates(#date(2021, 1, 1), 2000, #duration(1, 0, 0, 0)), ConvertedIntoTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), RenamedColumnDate = Table.RenameColumns(ConvertedIntoTable, {{"Column1", "Date"}}), AddColumnMonth = Table.TransformColumnTypes(Table.AddColumn(RenamedColumnDate, "Month", each Date.Month([Date])), {{"Month", Int64.Type}}), AddColumnYear = Table.TransformColumnTypes(Table.AddColumn(AddColumnMonth, "Year", each Date.Year([Date])), {{"Year", Int64.Type}}), AddColumWeekOfYear = Table.TransformColumnTypes(Table.AddColumn(AddColumnYear, "WeekOfYear", each Date.WeekOfYear([Date])), {{"WeekOfYear", Int64.Type}}), AddColumnMonthNameShort = Table.TransformColumnTypes(Table.AddColumn(AddColumWeekOfYear, "MonthNameShort", each Date.ToText([Date], "MMM")), {{"MonthNameShort", type text}}), AddColumnWeekDayName = Table.TransformColumnTypes(Table.AddColumn(AddColumnMonthNameShort, "WeekDayName", each Text.Start(Date.DayOfWeekName([Date]), 2)), {{"WeekDayName", type text}}), AddColumnWeekDayNum = Table.AddColumn(AddColumnWeekDayName, "WeekDayNum", each Date.DayOfWeek([Date])+1), ChangeDataType = Table.TransformColumnTypes(AddColumnWeekDayNum, {{"Date", type date}, {"WeekDayNum", Int64.Type}}), AddCoulmnPeriodYearMonth = Table.AddColumn(ChangeDataType, "Year-Month", each Number.ToText([Year]) & "-" & [MonthNameShort]), AddColumnPeriodYearMonthSort = Table.AddColumn(AddCoulmnPeriodYearMonth, "Year-Month (sort)", each [Year] * 100 + [Month]), AddColumnDateOfRefresh = Table.AddColumn(AddColumnPeriodYearMonthSort, "DateOfRefresh", each DateTime.LocalNow()) in AddColumnDateOfRefreshIt will provide you with a proper date table. If the granualrity is not right for you then you can delete columns and remove duplicate OR use the group by feauter.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
-----------------------------------------------------
Hi Chinh_Dac ,
Please check the formula:
Column = RANKX('calendar',YEAR('calendar'[Date])*100+MONTH('calendar'[Date]),,ASC,Dense)Hi Jay,
It works perfectly. Thank you so much for your help.
