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 another measure so I can have a relationship with a date table

RHB Leads = 
CALCULATE([RHB Leads - Count],
USERELATIONSHIP('Opportunity'[RHB Date of Enquiry], 'Full Date'[Full Date]))

 

 

So what I want to do is include the filter from the first image into the 'Leads (RHB) - Count' measure.

 

On the visual above I will also be looking at previous fiscal years, so for example if I'm looking at 19/20 Fiscal Year, then add RHB Created is on or after 01/04/2019

 

19/20 FY = 01/04/2019 Created On

18/19 FY = 01/04/2018 Created On

17/18 FY = 01/04/2017 Created On

 

Can anyone help me with this?

 

Thanks,

Mike

Need Help
Tips and Tricks
Tutorial Requests

8 Replies

  • 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.