Work a Date Filter into a Measure

[michael_knight]

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

[Anonymous]

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.

[michael_knight]

Hi @Anonymous 

Thanks for the help, wasn't successful with that unfortunatly. After doing that I didn't get any results

I'll add the PBIX file so you can have a look if you want to have a look 

https://www.dropbox.com/s/ojxkg7fj5rjdf1i/Acquisitions%20-%20Community.pbix?dl=0

Thanks,

Mike

[Anonymous]

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]

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]

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

[michael_knight]

Hi, 

Still struggling on this issue

Can anyone help?

Thanks,

Mike

[michael_knight]

Bump

[michael_knight]

Still struggling with this if anyone can help.

Thanks,

Mike