topic Calculating the Sum of Active Units Between Two Dates in DAX Commands and Tips

Calculating the Sum of Active Units Between Two Dates

gsd217 — Tue, 02 Feb 2021 17:42:11 GMT

*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-p/446810) 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.

Re: Calculating the Sum of Active Units Between Two Dates

Gabriel_Walkman — Tue, 02 Feb 2021 19:09:11 GMT

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 )

Re: Calculating the Sum of Active Units Between Two Dates

gsd217 — Wed, 03 Feb 2021 01:31:54 GMT

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])) )