Adding a date column

[rohitchouhan]

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.

[mahoneypat]

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

[stevedep]

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/dimen...

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

[stevedep]

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. 

[rohitchouhan]

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

[harshnathani]

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/1143...

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)

[rohitchouhan]

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

[harshnathani]

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)

[stevedep]

@harshnathani , we responded almost at the same time 🙂