Forum Discussion

Sara_J's avatar
Sara_J
Frequent Visitor
4 years ago
Solved

Rolling average and dax code for other excel formulas

Hi,   I have 3 tables :  Dim_Weeks, Fact1 and Fact2 joined by wk_num Need dax code for the F,G,H Fact 1:                                       Fact2: col-   A B   C D row Wk_num vo...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi Sara_J ,

    % vs F1F2 = divide(RELATED(Table2[vol3]),[vol1],0)

rolling last 4 weeks =
AVERAGEX (
    FILTER (
        'Table1',
        [Wk_num_1] <= EARLIER ( Table1[Wk_num_1] )
            && [Wk_num_1]
                >= EARLIER ( Table1[Wk_num_1] ) - 3
    ),
    [% vs F1F2]
)

Proj% =
DIVIDE (
    DIVIDE ( RELATED ( Table2[vol3] ), [rolling last 4 weeks] ) - [vol2],
    [vol2],
    0
)

Projected =
IF (
    [vol1] = 0,
    DIVIDE ( RELATED ( Table2[vol3] ), [rolling last 4 weeks] ),
    IF (
        AND (
            [vol1] > 0,
            CALCULATE (
                MAX ( 'Table1'[vol1] ),
                FILTER ( 'Table1', [Wk_num_1] = EARLIER ( Table1[Wk_num_1] ) + 1 )
            ) = 0
        ),
        [vol1],
        BLANK ()
    )
)

    Result:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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

v-chenwuz-msft's avatar
v-chenwuz-msft
Community Support
4 years ago

Hi Sara_J ,

 

A little confused, row 6 and 7's A is 0, how is E(D6/A6) calculated?

 

Best Regards

Community Support Team _ chenwu zhu

Sara_J's avatar
Sara_J
Frequent Visitor
4 years ago

Thanks for the response , there was typo. the correct 

EFGH
% vs F1F2:D4/A4rolling last 4 weeks: average (E1:E4)Proj%:((D4/F4)-B4)/B4Projected:IF(A4=0,D4/F4,IF(AND(A4>0,A5=0),A4,NA()))

Expected value for the row 6 and 7 is 

84%98%-6%#N/A
93%88%16%393
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Sara_J ,

    % vs F1F2 = divide(RELATED(Table2[vol3]),[vol1],0)

rolling last 4 weeks =
AVERAGEX (
    FILTER (
        'Table1',
        [Wk_num_1] <= EARLIER ( Table1[Wk_num_1] )
            && [Wk_num_1]
                >= EARLIER ( Table1[Wk_num_1] ) - 3
    ),
    [% vs F1F2]
)

Proj% =
DIVIDE (
    DIVIDE ( RELATED ( Table2[vol3] ), [rolling last 4 weeks] ) - [vol2],
    [vol2],
    0
)

Projected =
IF (
    [vol1] = 0,
    DIVIDE ( RELATED ( Table2[vol3] ), [rolling last 4 weeks] ),
    IF (
        AND (
            [vol1] > 0,
            CALCULATE (
                MAX ( 'Table1'[vol1] ),
                FILTER ( 'Table1', [Wk_num_1] = EARLIER ( Table1[Wk_num_1] ) + 1 )
            ) = 0
        ),
        [vol1],
        BLANK ()
    )
)

    Result:

     

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

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