Rolling Average 5 Week Part 2

[EnochS]

In my previous help request, we found the solution, and I was able to replicate this process with other tables successfully. For some reason, though, I'm having trouble replicating this same process to a third table, Data Here. Maybe I've been looking at it too much to not be able to see the simple problem (hopefully it's an easy fix) and thought maybe someone from the PBI community could spot it:

% Exp Sum is not necessary, i think, since the custom column of % Expense exists.. I was just trying to copy the other solutions*

% Exp Sum = SUM(T_Revenue_Expense_Data[% Expense]) 

% Exp 5W Avg 2 = calculate( 

AVERAGE(T_Revenue_Expense_Data[% Exp Sum]) ,

DATESINPERIOD (
        T_Revenue_Expense_Data[Date],
        LASTDATE ( T_Revenue_Expense_Data[Date].[Date]),
        -35,
        DAY
    )
)

I'm trying to create an average of the last 5 weeks expense to revenue ratio/percentage..

I want to make it look like this:

Any details will help.

@v-ljerr-msft

Thank you!

[Eric_Zhang · Accepted solution]

@EnochS

Try

% Exp Sum = 
SUMX (
    FILTER (
        T_Revenue_Expense_Data,
        T_Revenue_Expense_Data[Date] = EARLIER ( T_Revenue_Expense_Data[Date] )
    ),
    T_Revenue_Expense_Data[% Expense]
)

% Exp 5W Avg 2 = 
AVERAGEX (
    FILTER (
        T_Revenue_Expense_Data,
        T_Revenue_Expense_Data[Date]
            >= EARLIER ( T_Revenue_Expense_Data[Date] ) - 35
            && T_Revenue_Expense_Data[Date] <= EARLIER ( T_Revenue_Expense_Data[Date] )
    ),
    T_Revenue_Expense_Data[% Exp Sum]
)

[EnochS]

@Eric_Zhang,

Thank you!!

For educational purposes, was there a specific reason why the method I was trying to use didn't work like it did in the other tables?