How to calculate week differences between different data type

[Karnik_]

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

[parry2k]

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

[Karnik_]

Hi please check the post I have uploaded

[Anonymous · Accepted solution]

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.