Forum Discussion

wi11iamr's avatar
wi11iamr
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Create DateKey, but with only one entry per month

Hi there,

 

Using the Calendar function I can happily create datekey tables which have anentry for each day of a month.

I however need to create a datekey that only has the 1st day of each month, such as:

2017-01-01

2017-02-01

2017-03-01

...

 

instead of 

2017-01-01

2017-01-02

2017-01-03

2017-01-04

...

 

I suspect a custom function may be required to achieve this, however I'm at a bit of a loss in trying to achieve this.

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi wi11iamr,

     

    In addition to Pfister's solution, you can also use STARTOFMONTH Function (DAX) to:

     

    Add a new calculate column to your existing Calendar table.

    First Day of Month = STARTOFMONTH('Date'[Date])

    Or you can create a new table with the new Datekey column from your existing Calendar table.

    Table = 
DISTINCT (
    SELECTCOLUMNS ( 'Date', "NewDateKey", STARTOFMONTH ( 'Date'[Date] ) )
)

     

    Regards

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi wi11iamr

     

    Use this formula in Power Query:

     

     #"Added Custom" = Table.AddColumn(<YOUR LAST STEP>, "DateKey", each Date.StartOfMonth([YOUR_COLUMN]))

     

     

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi wi11iamr,

     

    In addition to Pfister's solution, you can also use STARTOFMONTH Function (DAX) to:

     

    Add a new calculate column to your existing Calendar table.

    First Day of Month = STARTOFMONTH('Date'[Date])

    Or you can create a new table with the new Datekey column from your existing Calendar table.

    Table = 
DISTINCT (
    SELECTCOLUMNS ( 'Date', "NewDateKey", STARTOFMONTH ( 'Date'[Date] ) )
)

     

    Regards

    • wi11iamr's avatar
      wi11iamr
      Icon for Advocate II rankAdvocate II
      9 years ago

      Thanks Anonymous and v-ljerr-msft.

      The StartOfMonth function put me on the right track and I liked the possible conciseness of using the "DISTINCT (SelectColumns..." approach, however while struggling with this I came to the realisation that creating the calendar table manually, as opposed to having it as a Query, introduced a new problem in that I'm not able to join a data query with a data table.

       

      I since pursued an option to create my Calendar data table as a query instead, and came across a great function from DataBear to create a dynamic data query.

      Power BI Tip : Dynamic Calendar Table (Power Query)