topic Re: Adding a date column in DAX Commands and Tips

Adding a date column

rohitchouhan — Fri, 12 Jun 2020 14:39:21 GMT

I want to add a new column as a date index, which starts with 03 April 2018 or any particular date and goes on till the current date.
how to add this thing through DAX as a column.I got some DAX expression but all those creating a new table for it.

Re: Adding a date column

Anonymous — Fri, 12 Jun 2020 15:45:38 GMT

Hi rohitchouhan ,

Your requirement is not very clear.

Do you want an index column against the date. i.e 1 for Dates 3rd April, 2 for Dates 4th April and so on.

If so, please see this https://community.powerbi.com/t5/Community-Blog/Getting-Previous-Values-in-Power-BI-Part-1/ba-p/1143370

Go to the section for creating a index column using rankx.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Adding a date column

stevedep — Fri, 12 Jun 2020 15:45:51 GMT

If you are looking to create a data table with an integer as your key. You perhaps don't need an index key. Just join on the date integer.

Date = ADDCOLUMNS ( CALENDAR (DATE(2018;4;1); DATE(2025;12;31)); "DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" ); "Year"; YEAR ( [Date] ); "Monthnumber"; FORMAT ( [Date]; "MM" ); "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" ); "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" ); "MonthNameShort"; FORMAT ( [Date]; "mmm" ); "MonthNameLong"; FORMAT ( [Date]; "mmmm" ); "DayOfWeekNumber"; WEEKDAY ( [Date] ); "DayOfWeek"; FORMAT ( [Date]; "dddd" ); "DayOfWeekShort"; FORMAT ( [Date]; "ddd" ); "Quarter"; "Q" & FORMAT ( [Date]; "Q" ); "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" ) )

Mind the data as integer colomn.

Please mark as solution if this helps you.

Kind regards,

Steve.

Re: Adding a date column

stevedep — Fri, 12 Jun 2020 15:46:46 GMT

Anonymous , we responded almost at the same time 🙂

Re: Adding a date column

rohitchouhan — Sat, 13 Jun 2020 15:38:40 GMT

Hello, thanks for the prompt reply,
i just want to add a column in my current table which starts from a particular date and goes on till today's date.
just like the calendar.I used some DAX but all those creating a separate table, not adding column to expecting table

Re: Adding a date column

rohitchouhan — Sat, 13 Jun 2020 15:39:41 GMT

Re: Adding a date column

Anonymous — Sat, 13 Jun 2020 16:02:57 GMT

HI rohitchouhan

Create an Index Column in Power Query.

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Adding a date column

mahoneypat — Sun, 14 Jun 2020 01:31:38 GMT

Please try this expression in your calculated column. It should count from 1 to today starting with the minimum date in the table. Note you will have duplicated values where you have duplicate days (so not really an "index").

Day Number = var mindate = MIN(Table[Created Date Time) // or you could hard code it with DATE(2018,4,3)

Return DATEDIFF(mindate, Table[Created Date Time], DAY) +1

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Adding a date column

stevedep — Sun, 14 Jun 2020 09:04:42 GMT

Hi,

As mr Kimball says:

"To facilitate partitioning, the primary key of a date dimension can be more meaningful, such as an integer representing YYYYMMDD, instead of a sequentially-assigned surrogate key"

https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/calendar-date-dimension/

So go to modelling -> new table -> and insert the code I shared earlier.