Rolling average and dax code for other excel formulas

[Sara_J]

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	vol1	vol2		Wk_num	vol3
1	157	345	325		157	455
2	158	396	351		158	371
3	159	375	468		159	313
4	160	379	344		160	319
5	161	393	356		161	364
6	162	0	423		162	413
7	163	0	380		163	349
8	164	0	233		164	339

Examples used in excel formula:

E	F	G	H
% vs F1F2:D6/A6	rolling last 4 weeks: average (E3:E6)	Proj%:((D6/F6)-B6)/B6	Projected: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

[v-chenwuz-msft]

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]

Thanks for the response , there was typo. the correct 

E	F	G	H
% vs F1F2:D4/A4	rolling last 4 weeks: average (E1:E4)	Proj%:((D4/F4)-B4)/B4	Projected: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 · Accepted solution]

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.