How to add a new column to transfer the new WW calucalation?

[marry]

Hi everyone,

Would like to seek for the help.

How to add a new column and have transfer to the new WW caluculation? I have particular data would like to have the new WW display.

Logic: If Index=Qty, then new WW= WW+2; the rest keep the AS-IS WW vlaue

Expected Result:

Best regards,

Marry

[Anonymous · Accepted solution]

Hi @marry ,

Please try to create column with below dax formula:

RK = RANKX(FinanceCalendayDayExtView,[Fiscal Year Work Week],,DESC,Dense)

New Year =
VAR _index = [Index]
VAR cur_ww = [WW]
VAR cur_wwnum =
    INT ( RIGHT ( [WW], 2 ) )
VAR cur_year = [Year ]
VAR _a =
    CALCULATE (
        MAX ( FinanceCalendayDayExtView[RK] ),
        FILTER (
            ALL ( FinanceCalendayDayExtView ),
            [FiscalYear] = cur_year
                && [WorkWeek] = cur_wwnum
        )
    )
VAR _year =
    CALCULATE (
        MAX ( FinanceCalendayDayExtView[FiscalYear] ),
        FILTER ( ALL ( FinanceCalendayDayExtView ), [RK] = _a - 2 )
    )
VAR _result =
    SWITCH (
        TRUE (),
        _index = "BOH", [Year ],
        _index = "Qty"
            && cur_ww = "WW99", [Year ],
        _index = "Qty", _year
    )
RETURN
    _result

New Month =
VAR _index = [Index]
VAR cur_ww = [WW]
VAR cur_wwnum =
    INT ( RIGHT ( [WW], 2 ) )
VAR cur_year = [Year ]
VAR cur_month = [Month]
VAR _a =
    CALCULATE (
        MAX ( FinanceCalendayDayExtView[RK] ),
        FILTER (
            ALL ( FinanceCalendayDayExtView ),
            [FiscalYear] = cur_year
                && [WorkWeek] = cur_wwnum
        )
    )
VAR _month =
    INT (
        RIGHT (
            CALCULATE (
                MAX ( FinanceCalendayDayExtView[YearMonth] ),
                FILTER ( FinanceCalendayDayExtView, [RK] = _a - 2 )
            ),
            2
        )
    )
VAR _result =
    SWITCH (
        TRUE (),
        _index = "BOH", cur_month,
        _index = "Qty"
            && cur_ww = "WW99", cur_month,
        _index = "Qty", _month
    )
RETURN
    _result

New WW =
VAR _index = [Index]
VAR cur_ww = [WW]
VAR cur_wwnum =
    INT ( RIGHT ( [WW], 2 ) )
VAR cur_year = [Year ]
VAR _a =
    CALCULATE (
        MAX ( FinanceCalendayDayExtView[RK] ),
        FILTER (
            ALL ( FinanceCalendayDayExtView ),
            [FiscalYear] = cur_year
                && [WorkWeek] = cur_wwnum
        )
    )
VAR _ww =
    INT (
        CALCULATE (
            MAX ( FinanceCalendayDayExtView[WorkWeek] ),
            FILTER ( FinanceCalendayDayExtView, [RK] = _a - 2 )
        )
    )
VAR ww_1 =
    SWITCH ( TRUE (), LEN ( _ww ) = 1, "WW0" & _ww, LEN ( _ww ) = 2, "WW" & _ww )
VAR _result =
    SWITCH (
        TRUE (),
        _index = "BOH", cur_ww,
        _index = "Qty"
            && cur_ww = "WW99", cur_ww,
        _index = "Qty", ww_1
    )
RETURN
    _result

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marry]

Hello @Anonymous ,

Appreciated your help!!!!：）

Many thanks for the detailed DAX and attached file.

[Bmejia]

Here are two possible methods.

Assuming your data does not change, your text value start with 2 characters and the number is aways the third character in this column you can use something like this.
NE_WW2 =
Var WW_Value= left(YOURTABLE[WW],2)
Var WW_Num = MID(YOURTABLE[WW],3,5)+2
return
IF(
(CONTAINSSTRING(YOURTABLE[WW],"WW")=TRUE())
&& YOURTABLE[Index]="QTY"
,WW_Value & WW_Num
,YOURTABLE[WW])

2nd Method prefer to use as it will adjust more easily to the data

Transform the data

1st Duplicate the WW column

2nd Split the column By Non-Digit to Digit will split text and numbers

Change the number from text to numeric if text format (In my example is WW2.2)

Add a custom column called “New WW”

=If Text.Contains([WW], "WW") and [Index]="Qty" then [WW2.1]&Number.ToText([WW2.2]+2)

  else [WW]

You can keep the two new columns that were split or just do not choose the column during transformation.

[marry]

Hello @Bmejia ,

Thanks for the professional and deatiled solution explanation.

I just found out I missed to consider the cross year situation.

The requirment is more like I would like to move back 2 weeks( add 2 weeks) for the pariticular data (Index=Qty), and add some new columns to reflect the new WW, Month and Year.

I have another table in my Power BI is the calender raw data. Would like to refer to this calender table and reflect the result in the new added WW, Month, Year columns. 

Expected Result:

(Some Index= Qty data, the WW= WW99, for those data would also keep the AS-IS value for the New Year, Month & WW columns)

Calender Table in my Power BI: (Please check my PBI file)

It seems I cannot insert the PBI file here directly, I put it in google drive, please let me know whether you can see it.) Thanks for the help!!!!

 https://drive.google.com/file/d/1SslxfMwmjQ_Yl8np31CFKoQ8-pL-QIQy/view?usp=sharing 

Best regards,

Marry