Adding customer specific date to date dim

[jpt1228]

Hello, am building a data model for a national retailer and in their system they report the date as Year Month Week and Week Number as formatted below:

I want to convert this to a date the date tables can recognize. I think the simplest way is going to be to create a helper table where I build in excel and import between the model date table and the retailer fact table.

Here is what their calendar looks like

[v-ljerr-msft]

Hi @jpt1228,

I assume you already have an individual and continuous Calendar table called "Date" like below.

Then you should be able to use the formula below to create a calculate column in your data table to convert the custom date to a normal date. 

Column = 
VAR year =
    VALUE ( LEFT ( Table1[Customer Date], 4 ) )
VAR month =
    SWITCH (
        MID ( Table1[Customer Date], 6, 3 ),
        "Jan", 1,
        "Feb", 2,
        "Mar", 3,
        "Apr", 4,
        "May", 5,
        "Jun", 6,
        "Jul", 7,
        "Aug", 8,
        "Sep", 9,
        "Oct", 10,
        "Nov", 11,
        "Dec", 12,
        0
    )
VAR weekno =
    VALUE ( RIGHT ( Table1[Customer Date], 1 ) )
VAR firstDateOfMonth =
    DATE ( year, month, 1 )
VAR weeknum =
    WEEKNUM ( firstDateOfMonth, 2 )
RETURN
    CALCULATE (
        LASTDATE ( 'Date'[Date] ),
        FILTER ( 'Date', WEEKNUM ( 'Date'[Date], 2 ) = weeknum + weekno - 1 )
    )

Regards

[jpt1228]

Hi @v-ljerr-msft - This is partially working. I am able to convert into month and day. However the Year is coming in as 2025 rather than 2017.  I just created the calendar table from 2000-2025

Target Date to DateDim =
VAR year =
VALUE ( LEFT ( 'Customer Sales'[Target Date], 4 ) )
VAR month =
SWITCH (
MID ( 'Customer Sales'[Target Date], 6, 3 ),
"Jan", 1,
"Feb", 2,
"Mar", 3,
"Apr", 4,
"May", 5,
"Jun", 6,
"Jul", 7,
"Aug", 8,
"Sep", 9,
"Oct", 10,
"Nov", 11,
"Dec", 12,
0
)
VAR weekno =
VALUE ( RIGHT ( 'Customer Sales'[Target Date], 1 ) )
VAR firstDateOfMonth =
DATE ( year, month, 1 )
VAR weeknum =
WEEKNUM ( firstDateOfMonth, 2 )
RETURN
CALCULATE (
LASTDATE ( 'DateDim'[Date] ),
FILTER ( 'DateDim', WEEKNUM ( 'DateDim'[Date], 2 ) = weeknum + weekno - 1 )
)

[v-ljerr-msft · Accepted solution]

Hi @jpt1228,

My mistake! The formula below should work. 

Column = 
VAR year =
    VALUE ( LEFT ( Table1[Customer Date], 4 ) )
VAR month =
    SWITCH (
        MID ( Table1[Customer Date], 6, 3 ),
        "Jan", 1,
        "Feb", 2,
        "Mar", 3,
        "Apr", 4,
        "May", 5,
        "Jun", 6,
        "Jul", 7,
        "Aug", 8,
        "Sep", 9,
        "Oct", 10,
        "Nov", 11,
        "Dec", 12,
        0
    )
VAR weekno =
    VALUE ( RIGHT ( Table1[Customer Date], 1 ) )
VAR firstDateOfMonth =
    DATE ( year, month, 1 )
VAR weeknum =
    WEEKNUM ( firstDateOfMonth, 2 )
RETURN
    CALCULATE (
        LASTDATE ( 'Date'[Date] ),
        FILTER ( 'Date', WEEKNUM ( 'Date'[Date], 2 ) = weeknum + weekno - 1 && YEAR('Date'[Date])=year)
    )

Regards

[jpt1228]

Such smart people here. That works

Thanks @v-ljerr-msft

[Peter_R]

As an FYI, there are numerous blog discussions around create custom calendars. It's typically done on the query side. Here are some examples:

https://www.excelguru.ca/blog/2016/01/06/creating-a-custom-calendar-in-power-query/

https://powerpivotpro.com/category/skill-areas/custom-calendars/

[jpt1228]

Hi @Peter_R I'm not sure I follow your logic. I don't think I need to crerate a custom calendar as much as convert text and numerals to a calendar date. @v-ljerr-msft Got me close to what I am looking for but the year was not coming in correctly. That is what I am looking to resolve.

Thanks

Jon