Forum Discussion

michael_knight's avatar
michael_knight
Post Prodigy
6 years ago

Work a Date Filter into a Measure

Hi,   I've got this filter on one of my visuals  This is the measure that I've used for the visual above  Leads (RHB) - Count = COUNTA(Opportunity[RHB Date of Enquiry]) I also have anot...
Need Help
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi @michaelknight94,

 

If I understand you correctly, you can modify your measure as below:

RHB Leads =

CALCULATE (

    [RHB Leads - Count],

    FILTER (

        Opportunity,

        FORMAT ( Opportunity[RHB Date of Enquiry], "YYYYMM" ) >= "201704"

            && FORMAT ( Opportunity[RHB Date of Enquiry], "YYYYMM" ) < "201804"

    ),

    USERELATIONSHIP ( 'Opportunity'[RHB Date of Enquiry], 'Full Date'[Full Date] )

)

Or create a slicer and result would be shown as below:

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi michael_knight ,

     

    Thank you for your Pbix, I modify the measure and the result would be show as below:

    RHB Leads =
    CALCULATE(
    [RHB Leads - Count],FILTER(Opportunity,FORMAT(Opportunity[RHB Created On],"MMDD")>="0401"),
    USERELATIONSHIP('Opportunity'[RHB Date of Enquiry], 'Full Date'[Full Date]))
    Let me know if the result is what you want.
     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    • michael_knight's avatar
      michael_knight
      Post Prodigy
      6 years ago

      Hi Anonymous ,

       

      Thanks for giving it a go. It works if you're just looking at the current Fiscal Year but if you try to use the Fiscal Year date slicer and look at the previous years it doesn't work. 

      Getting closer though!

       

      Thanks,

      Mike

      • michael_knight's avatar
        michael_knight
        Post Prodigy
        6 years ago

        Hi, this is stil an issue. 

         

        It works fine for the current fiscal years 19/20, however if I go back to the fiscal year 18/19 that's when I get the discrepancies in the data. 

         The left visual is when I tried using the filter that was suggested above

        RHB Leads = 
CALCULATE(
[RHB Leads - Count],FILTER(Opportunity,FORMAT(Opportunity[RHB Created On],"MMDD")>="0401"),
USERELATIONSHIP('Opportunity'[RHB Date of Enquiry], 'Full Date'[Full Date]))

        The right visual is when I put in a filter on in the filter plain, like so

         

        Thanks,

        Mike