Forum Discussion

jpt1228's avatar
jpt1228
Icon for Responsive Resident rankResponsive Resident
8 years ago
Solved

Adding customer specific date to date dim

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

 

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

    Hi jpt1228,

     

    My mistake! The formula below should work. :smileyhappy:

    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

6 Replies

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

    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's avatar
      jpt1228
      Icon for Responsive Resident rankResponsive Resident
      8 years ago

      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's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi jpt1228,

         

        My mistake! The formula below should work. :smileyhappy:

        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