Forum Discussion

gco's avatar
gco
Resolver II
6 years ago
Solved

Help with filters

Hi there,   I have a report that has been working last year, and this year it broke because of our 2020 data.  Can you please help me figure out what filter to use to be able to display the correct...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi gco ,

     

    Could you please try to change the ALL to ALLSELECT Function?  If it does not work, could you please also provide the mockup Variance table based on fake data?

     

    %ToGoalYTD =
DIVIDE (
    SUM ( Goal[Balance] ),
    CALCULATE (
        SUM ( Goal[Value] ),
        FILTER (
            ALLSELECTED ( Variance ),
            Variance[MonthEndDate] = ENDOFYEAR ( Variance[MonthEndDate] )
        ),
        VALUES ( Variance[BranchName] )
    ),
    0
)


    Best regards,

     

gco's avatar
gco
Resolver II
6 years ago
BranchNameMonthEndDateBalanceValue
Branch11/31/2019500600
Branch112/31/2019750800
Branch11/31/2020 700
Branch112/31/2020 800
Branch21/31/2019400700
Branch212/31/2019600800
Branch21/31/2020 900
Branch212/31/2020 1000

Here is a sample table.  

 

%ToGoalYTD (Jan 2019) should be = DIVIDE(500/800).  With the measure i was using, it is coming up as DIVIDE(500/1600).  The YEAREND is combining 12/31/2019 and 12/31/2020 for some reason.

 

Thank you

Glen

 

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi gco ,

 

Could you please try to change the ALL to ALLSELECT Function?  If it does not work, could you please also provide the mockup Variance table based on fake data?

 

%ToGoalYTD =
DIVIDE (
    SUM ( Goal[Balance] ),
    CALCULATE (
        SUM ( Goal[Value] ),
        FILTER (
            ALLSELECTED ( Variance ),
            Variance[MonthEndDate] = ENDOFYEAR ( Variance[MonthEndDate] )
        ),
        VALUES ( Variance[BranchName] )
    ),
    0
)


Best regards,

 

  • gco's avatar
    gco
    Resolver II
    6 years ago

    Hi v-lid-msft ,

     

    Your solution was spot on!!!  I went and changed my measure from ALL to ALLSELECTED and it shows the correct calculations now.  Thank you again for your expertise and for your help on resolving my issue.  I owe you big time.

     

    Thank you again

    Glen