Forum Discussion

wi11iamr's avatar
wi11iamr
Advocate 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, ...
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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft 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
Advocate 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)