Create Index Column Based on Month Year Column

[Chinh_Dac]

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

[v-jayw-msft · Accepted solution]

Hi @Chinh_Dac ,

Please check the formula:

Column = RANKX('calendar',YEAR('calendar'[Date])*100+MONTH('calendar'[Date]),,ASC,Dense)

[Chinh_Dac]

Hi Jay,
It works perfectly. Thank you so much for your help.

[Mikelytics]

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.

-----------------------------------------------------

LinkedIn

[Chinh_Dac]

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.

[Mikelytics]

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
    AddColumnDateOfRefresh

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

-----------------------------------------------------

LinkedIn

[Chinh_Dac]

Mikelytics,

Thank you so much for your response. Unfortunately, your Query does not have the Month Year Index column I need.

[Mikelytics]

HI @Chinh_Dac 

it has:

Best regards

Michael

[Mikelytics]

@Chinh_Dac 

As I described above you can delete columns you do not need and adjust the table like this:

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()),
    #"Removed Other Columns" = Table.SelectColumns(AddColumnDateOfRefresh,{"Year-Month (sort)"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

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.

-----------------------------------------------------

LinkedIn

[Chinh_Dac]

Good morning, Micheal.
Thank you for your suggestion. However, I want to keep the calendar table at the date level, so it does not return correctly. 

Best Regards,

Chinh Ho