Forum Discussion

mmanwaring's avatar
mmanwaring
Resolver I
4 years ago

Filter context breaking my calculation

Hi All,

Hope someone can shed some light on my problem.

I have a stock movement calculation which is pivoted by col = sku and row= year.
The problem is that the table has to be filtered to the correct movement types.

example.
This works for running total:

CALCULATE(SUM('Table'[TrueQuantity]), DimDates[Date] <= MaxDate , ALL(DimDates) )

 



this does not:

CALCULATE(SUM(MovementsInOut[TrueQuantity]), FILTER( MovementsInOut , movementsInOut[movementtype] IN {100,392,227,235,225}), DimDates[Date] <= MaxDate , ALL(DimDates) )




I would like to avoid creating an additional table to the model so if the filter can work within the calculation that would be perfect

thank you

Mike

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    mmanwaring , Try like

    CALCULATE(SUM(MovementsInOut[TrueQuantity]), FILTER( MovementsInOut , movementsInOut[movementtype] IN {100,392,227,235,225}), filter(all(DimDates) ,DimDates[Date] <= MaxDate ))

    • mmanwaring's avatar
      mmanwaring
      Resolver I
      4 years ago

      Hi, Thank you for your reply.
      It does however not change the results.
      Mike

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        mmanwaring ,
        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.