Measure to filter table based on 2 date fields (purchase, disposal)

[REWINDER]

Hi everyone,

Though I thought my problem was solved earlier this week: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measure-to-filter-rows-based-on-two-dates-beg...

It unfortunately was not. (cannot seem to UNsolve that topic) 

I am looking to get a measure with Active vehicles number, ClassStructure, By year (>= [PurchaseDate], <=[ReplacementDate], . 

Presented but NOT correctly working option was: 

------------------------------------------------------------------------------------- 

#Vehicles Active =CALCULATE (
COUNTROWS ( FleetActivity ),
FILTER (ALL ( FleetActivity ),
FleetActivity[PurchaseDate] <= MAX ( dimDate2[Date_pk] )
&& FleetActivity[ReplacementDate] >= MIN ( dimDate2[Date_pk] )
))
------------------------------------------------------------------------------------- 

>> My thinking is that the problem lies with the relationship to the Date table ( 1. with [PurchaseDate] and 2. with [ReplacementDate]), So I tried Userelationship: but it gives me the error that it can only be used in a Calculate syntax.  

 ----------------------------------------------------------------------------------- 

 active vehicles userelationship = CALCULATE ( COUNTROWS ( FleetActivity ), FILTER ( ALL ( FleetActivity ), FleetActivity[PurchaseDate] <= MAX ( dimDate2[Date_pk] ) && Userelationship(FleetActivity[ReplacementDate],dimDate2[Date_pk]) >= MIN ( dimDate2[Date_pk] ) )) 
------------------------------------------------------------------------------------ 

Date table and relationships:
 

I wanted to share a Pbix test file, but unfortunately it is not supported. 
Also, Sorry I had to re-open this question: if anyone knows how to (UN)solve the other topic, happy to do that. 

Thank you in advance! 

[REWINDER · Accepted solution]

SOLUTION:

1. remove connections to date table, then:

#ClassActiveCount =

-- Calculate active totals

VAR currentDate =

MAX ( dimDate2[Date_pk] ) -- Saves the last visible date

Var MinCurrentDate =

MIN( dimDate2[Date_pk]) -- Saves first visible date

RETURN

CALCULATE (

COUNTROWS (FleetActivity), -- Computes Active count

FILTER (

FleetActivity,

( FleetActivity[PurchaseDate] <= currentDate -- Where Start date is before the last visible date

&& (FleetActivity[ReplacementDate] > MinCurrentDate -- Where End date is after the last visible date

|| FleetActivity[ReplacementDate] = BLANK()) -- -- Where date is null

)

))

[tamerj1]

Hi @REWINDER 
I assume the active relationship is with the FleetActivity[PurchaseDate] coulmn, then please try 

#Vehicles Active =
CALCULATE (
    COUNTROWS ( FleetActivity ),
    FleetActivity[PurchaseDate] <= MAX ( dimDate2[Date_pk] ),
    FleetActivity[ReplacementDate] >= MIN ( dimDate2[Date_pk] ),
    CROSSFILTER ( FleetActivity[PurchaseDate], dimDate2[Date_pk], NONE )
)

[REWINDER]

@tamerj1 ,
Thank you for the suggestion, but this solution too only counts total vehicles, and does not separate per Type.
Attempting to make a hybrid out of the three measures now.

[tamerj1]

Hi @REWINDER 
Would you please provide sample data and on the same screenshot above manually type the correct expected results?

[amitchandak]

@REWINDER , change as per need

active vehicles userelationship = CALCULATE ( CALCULATE ( COUNTROWS ( FleetActivity )Userelationship(FleetActivity[ReplacementDate],dimDate2[Date_pk]) )
, FILTER ( ALL ( FleetActivity ), FleetActivity[ReplacementDate] <= MAX ( dimDate2[Date_pk] ) && FleetActivity[ReplacementDate] >= MIN ( dimDate2[Date_pk] ) ))

You need the filter you have used only when you want filter purchase date not the replacement date 

this should do

CALCULATE ( COUNTROWS ( FleetActivity )Userelationship(FleetActivity[ReplacementDate],dimDate2[Date_pk]) )

example

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

[REWINDER]

@amitchandak , Thank you.

Though it gets the right values at the end of the date range,

at the beginning, it only starts counting values from the Replacement date, see figure 1. Figure 2 at end of Date range. (measures above table for reference).

So the measure on the right gets the numbers right, but is unable to split it by ClassStructure.