Create DateKey, but with only one entry per month

[wi11iamr]

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.

[v-ljerr-msft · Accepted solution]

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]

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)

[Anonymous]

Hi @wi11iamr

Use this formula in Power Query:

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