Calculating the Sum of Active Units Between Two Dates

[gsd217]

*Deleted from Desktop Discussion*

Hello, I am attempting to calculate the number of units which were active between the selected dates on a slicer. Each Unit has a "DATE_IN_SERVICE" and a "DATE_OUT_SERVICE" column. 

From, following this solution (https://community.powerbi.com/t5/Desktop/Use-date-slicer-to-check-if-date-falls-between-two-dates/m-...) My current DAX is the following (I am hung up on using MIN/MAX however, and think this may be the reason I'm not getting the expected results): 

Power_Unit_Count =
VAR SelectedDate = SELECTEDVALUE(DIM_DATE[date_date])

RETURN
IF(SelectedDate >= MIN(DIM_PUNIT_CUSTOM[DATE_IN_SERVICE]) && SelectedDate <= MAX(DIM_PUNIT_CUSTOM[DATE_OUT_SERVICE]),DISTINCTCOUNT(DIM_PUNIT_CUSTOM[UNIT_ID]),0)

Example of current Data structure

Expected Result is 112 total, by following the logic below:

 Filtering for Units which have a non-blank DATE IN SERVICE and DATE_OUT_SERVICE is either BLANK or Greater than the last date in the slicer.

[Gabriel_Walkman]

Hi!

I'd transform the date ranges into single dates. Ie in power query (if the time values are not important) change start and end into dates, then change them into integers, create a custom column Date = {[start]..[end]} and after that you should be able to extract the Date lists as separate rows. Now you can just plug in a relationship from you calendar table date to that Date and count id's or whatever for values.

Edit: oh yeah I think your method should work too. Try putting that SELECTEDVALUE(DIM_DATE[date_date]) on a card or something and see what happens. Maybe something like this could work, can't test right now:

Power_Unit_Count =
VAR _start = MIN( SELECTEDVALUES(DIM_DATE[date_date]) )
VAR _end = MAX( SELECTEDVALUES(DIM_DATE[date_date]) )
RETURN
calculate(
    DISTINCTCOUNT(DIM_PUNIT_CUSTOM[UNIT_ID],
    DIM_PUNIT_CUSTOM[DATE_IN_SERVICE] >= _start,
    DIM_PUNIT_CUSTOM[DATE_OUT_SERVICE] <= _end
)

[gsd217 · Accepted solution]

@Gabriel_Walkman  Thanks! For the most part it worked. I did have to make some modifications to suit my needs. 

PUnit_Count = 
VAR _start = FIRSTDATE(DIM_DATE[date_date])
VAR _end = LASTDATE(DIM_DATE[date_date])
RETURN
calculate(
    DISTINCTCOUNT(DIM_PUNIT_CUSTOM[UNIT_ID]),
    DIM_PUNIT_CUSTOM[DATE_IN_SERVICE] <= _end,
    DIM_PUNIT_CUSTOM[DATE_OUT_SERVICE] >= _end || ISBLANK(DIM_PUNIT_CUSTOM[DATE_OUT_SERVICE]),
   NOT(ISBLANK(DIM_PUNIT_CUSTOM[DATE_IN_SERVICE]))
)