Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Chinh_Dac
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @Chinh_Dac ,

 

Please check the formula:

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

vjaywmsft_0-1670229486504.png

 

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

9 REPLIES 9
v-jayw-msft
Community Support
Community Support

Hi @Chinh_Dac ,

 

Please check the formula:

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

vjaywmsft_0-1670229486504.png

 

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

Mikelytics
Resident Rockstar
Resident Rockstar

Hi @Chinh_Dac 

 

Please use the following approach

 

exemplary baset table:

Mikelytics_0-1669667701955.png

select your date table and go to

Add Column -> Index Column -> from 1

Mikelytics_1-1669667766799.png

Resul

Mikelytics_2-1669667800086.png

 

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

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Mikelytics,

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

HI @Chinh_Dac 

 

it has:

Mikelytics_0-1669671819098.png

 

Best regards

Michael

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

@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"

 

Mikelytics_1-1669672014763.png

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

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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. Screenshot 2022-11-29 082208.png

Best Regards,

Chinh Ho

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.