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- AB CD
rowWk_numvol1vol2 Wk_numvol3
1157345325 157455
2158396351 158371
3159375468 159313
4160379344 160319
5161393356 161364
61620423 162413
71630380 163349
81640233 164339

 

Examples used in excel formula:

EFGH
% vs F1F2:D6/A6rolling last 4 weeks: average (E3:E6)Proj%:((D6/F6)-B6)/B6Projected:IF(A6=0,D6/F6,IF(AND(A6>0,A7=0),A6,NA()))

Expected value for the row 6 and 7 is 

84%98%-6%#N/A
93%88%16%393

 

created calculated column for E:  

%F1 vs.F2 =
VAR NUMERATOR = Fact2[Vol3]
VAR DENOMINATOR = Fact1[Vol1]
RETURN
DIVIDE(NUMERATOR, DENOMINATOR)

 

need help in other projections from excel to dax code

 

Thanks

 

 

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.

3 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity 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
        Icon for Community Support rankCommunity 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.