Forum Discussion

rohitchouhan's avatar
rohitchouhan
Frequent Visitor
6 years ago

Adding a date column

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.

 

8 Replies

  • stevedep's avatar
    stevedep
    Memorable Member
    6 years ago

    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's avatar
      rohitchouhan
      Frequent Visitor
      6 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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)

    • stevedep's avatar
      stevedep
      Memorable Member
      6 years ago

      Anonymous , we responded almost at the same time 🙂

    • rohitchouhan's avatar
      rohitchouhan
      Frequent Visitor
      6 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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)

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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