Forum Discussion

Karnik_'s avatar
Karnik_
Icon for Helper I rankHelper I
3 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 n...
Need Help
  • Anonymous's avatar
    Anonymous
    3 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.

parry2k's avatar
parry2k
Icon for Super User rankSuper User
3 years ago

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

  • Karnik_'s avatar
    Karnik_
    Icon for Helper I rankHelper I
    3 years ago

    Hi please check the post I have uploaded

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 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.