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 t...
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

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

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

    Such smart people here. That works

     

    Thanks v-ljerr-msft