cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver II

## 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 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
1 ACCEPTED SOLUTION
Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Resolver II
 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

Community Support

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resolver II

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors