Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Last 4 week gross margin formula not working

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

 

 

Last four weeks Gross margin

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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