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 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
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,

     

3 Replies

  • 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