Last 4 week gross margin formula not working

[Anonymous]

Hi all,

The below formula is not giving desired output

Average Gross Margin Last Four Weeks Filter = CALCULATE(
                                  [Total Gross Margin Level 2],
                                      FILTER(ALL(PBIDateTable[Week number]),
       PBIDateTable[Week number] <= MAX(PBIDateTable[Week number])-1
    && PBIDateTable[Week number] >= MAX(PBIDateTable[Week number])-4))/4

[v-shex-msft]

Hi @Anonymous,

I think max function may break by all function , maybe you can try to use variable function:

Average Gross Margin Last Four Weeks Filter =
VAR _current =
    MAX ( PBIDateTable[Week number] )
RETURN
    CALCULATE (
        [Total Gross Margin Level 2],
        FILTER (
            ALL ( PBIDateTable ),
            PBIDateTable[Week number]
                <= _current - 1
                && PBIDateTable[Week number]
                >= _current - 4
        )
    )
        / 4

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng