Help with filters

[gco]

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 value?

I am calculating %ToGoalYTD (% of current month compared to year-end goal).  Since i have YTD goal for both 2019 and 2020, the filter below combines the YearEnd for 2019 and 2020, instead of just for the year 2019, if i set my slicer to use 2019.  I think i need to replace ENDOFYEAR(Variance[MonthEndDate]) with something else.

I wanted the yearend value to be of 2019 if i set my slicer to 2019.  And same with 2020.

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

Thank you

Glen

[gco]

BranchName	MonthEndDate	Balance	Value
Branch1	1/31/2019	500	600
Branch1	12/31/2019	750	800
Branch1	1/31/2020		700
Branch1	12/31/2020		800
Branch2	1/31/2019	400	700
Branch2	12/31/2019	600	800
Branch2	1/31/2020		900
Branch2	12/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 · Accepted solution]

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]

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