Count how many days between 2 measures is contained within filtered dates

[Anonymous]

I have created two measures minCheck and maxCheck, one to check the minimum date a campaign started and another to check the maximum date of the campaign.

I also have a date slicer from the calendar table, what I want to do is count how many days between minCheck and maxCheck consists within the date slicer.

So the date filtered is 27/06/2022 - 25/06/2023, so I need to return only the count of days that are within the selected date range which would be 96 days because 22/03/203 - 25/06/2023

 

[Anonymous · Accepted solution]

Hi @Anonymous ,

I modified the dax based on @PijushRoy 's and you can follow the steps below to fix the problem:
1.Add a mesure, DO NOT create relationships for dates in the slicer.

DateDiff = 
VAR selectmin =
    MIN ( 'Date Table'[Date] )
VAR selectmax =
    MAX ( 'Date Table'[Date] )
VAR _1 =
    IF ( selectmin <= [mincheck], [mincheck], selectmin )
VAR _2 =
    IF ( selectmax >= [maxcheck], [maxcheck], selectmax )
RETURN
    DATEDIFF ( _1, _2, DAY )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[Anonymous]

datediff using those dates will give 98 days, it needs to return 96 days because the user has filtered up to the 25th, so the 26th and 27th wont be included. So it needs to actually check whether the individual dates between those 2 measures exist within the selected date range

[PijushRoy]

Hi @Anonymous 

Please create a measure

DateDiff = DATEDIFF([MinDate],[MaxDate],DAY)

Let me know if that works for you

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush
Linkedin