Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter not working as expected

  Hi all. Iniatially posted this in the Dsktop section but wonder if it better fits here   I am trying to caculate the variance period on period per customer. My Period value is in table date My...
Help me please
  • sturlaws's avatar
    sturlaws
    6 years ago

    hi, Anonymous ,

     

    could you try to wrap an ALL() around 'Date Lookup' inside the filter, like this:

    Value2 =
VAR periodvalue =
    VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) )
RETURN
    CALCULATE (
        SUM ( 'Sales Invoice History'[STG_Gross_Profit_Less_BDM] ),
        FILTER (
            ALL ( 'Date LookUp' ),
            'Date LookUp'[Financial Period] = periodvalue + 1
        )
    )

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

hi, Anonymous ,

 

could you try to wrap an ALL() around 'Date Lookup' inside the filter, like this:

Value2 =
VAR periodvalue =
    VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) )
RETURN
    CALCULATE (
        SUM ( 'Sales Invoice History'[STG_Gross_Profit_Less_BDM] ),
        FILTER (
            ALL ( 'Date LookUp' ),
            'Date LookUp'[Financial Period] = periodvalue + 1
        )
    )

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Initially this didn't work for me but I then realised it was because I also had a Year field and the All on the filter was bypassing this page filter. By adding a filter on selected year into the value2 code I got this working perfectly. Thanks all for your help. For anyone else experiencing similar problems here is the final code that works for my exmple

    GP Variance =

    VAR periodvalue1 = VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) )

    VAR Value1 = CALCULATE ( sum('Sales Invoice History'[STG_Gross_Profit_Less_BDM]), FILTER ( 'Date LookUp', 'Date LookUp'[Financial Period] = periodvalue1 ) )

     

    VAR periodvalue2 = VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) -1 )

    VAR yearvalue2 = VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Year] ) )

     

    Var Value2 = CALCULATE ( sum('Sales Invoice History'[STG_Gross_Profit_Less_BDM]), FILTER ( all('Date LookUp'), 'Date LookUp'[Financial Period] = periodvalue2 && 'Date LookUp'[Financial Year] = yearvalue2 ) )

     

    Return IF(Value2 <> 0,Value1 - Value2,0)