Forum Discussion

Karnik_'s avatar
Karnik_
Helper I
2 years ago
Solved

How to calculate week differences between different data type

I have two columns
InvCom_Date = Which is a date type column
Workweek = Which has data like '23W34' which means year 2023 and week 34
now i want to calculate the difference in weeks.
Tried
for now i have tried splitting the 'workweek' column and extracting week no. and subtracting from the extracted weeknum of 'InvCom_Date'. But issue is handling the years if 'InvCom_Date' is in 2024 then the weekdifference is wrong. I tried a lot with measures I am not able to figure out how to handle year diffrence while calculating also the difference in data types i giving me issue in finding a solution

heres the sample data and expected output



Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Karnik_ ,

     

    I suggest you to create a Calendar table to help calculation.

    Calendar = 
VAR _STEP1 =
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "WeekStart",
            [Date] - WEEKDAY ( [Date], 2 ) + 1,
        "WeekYear",
            YEAR ( [Date] - WEEKDAY ( [Date], 2 ) + 1 )
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "ActualWeek",
            RANKX (
                FILTER ( _STEP1, [WeekYear] = EARLIER ( [WeekYear] ) ),
                [WeekStart],
                ,
                ASC,
                DENSE
            )
    )
VAR _STEP3 = 
ADDCOLUMNS(_STEP2,"Workweek",RIGHT([WeekYear],2)&"W"&FORMAT([ActualWeek],"00"))
RETURN
    _STEP3

    Then create a calculated column.

    Difference in No.of Weeks =
VAR _START =
    CALCULATE (
        MAX ( 'Calendar'[WeekStart] ),
        FILTER ( 'Calendar', 'Calendar'[Workweek] = EARLIER ( 'Table'[Workweek] ) )
    )
VAR _END =
    CALCULATE (
        MAX ( 'Calendar'[WeekStart] ),
        FILTER ( 'Calendar', 'Calendar'[Date] = EARLIER ( 'Table'[InvCom_Date] ) )
    )
RETURN
    DATEDIFF ( _START, _END, WEEK )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Karnik_ not fully sure what you are trying to achieve, paste sample data in the table format with the expected output.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Karnik_ ,

         

        I suggest you to create a Calendar table to help calculation.

        Calendar = 
VAR _STEP1 =
    ADDCOLUMNS (
        CALENDARAUTO (),
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "WeekStart",
            [Date] - WEEKDAY ( [Date], 2 ) + 1,
        "WeekYear",
            YEAR ( [Date] - WEEKDAY ( [Date], 2 ) + 1 )
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "ActualWeek",
            RANKX (
                FILTER ( _STEP1, [WeekYear] = EARLIER ( [WeekYear] ) ),
                [WeekStart],
                ,
                ASC,
                DENSE
            )
    )
VAR _STEP3 = 
ADDCOLUMNS(_STEP2,"Workweek",RIGHT([WeekYear],2)&"W"&FORMAT([ActualWeek],"00"))
RETURN
    _STEP3

        Then create a calculated column.

        Difference in No.of Weeks =
VAR _START =
    CALCULATE (
        MAX ( 'Calendar'[WeekStart] ),
        FILTER ( 'Calendar', 'Calendar'[Workweek] = EARLIER ( 'Table'[Workweek] ) )
    )
VAR _END =
    CALCULATE (
        MAX ( 'Calendar'[WeekStart] ),
        FILTER ( 'Calendar', 'Calendar'[Date] = EARLIER ( 'Table'[InvCom_Date] ) )
    )
RETURN
    DATEDIFF ( _START, _END, WEEK )

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.