Create a Measure in Power Pivot / DAX that calculates available equipment based on date ranges

Question

I am looking for unit counts when the equipment is not in the date range (start and end dates).

How do I make a measure "AvailUnitCount" to give me unit counts by category in the timeline dimension (say November 11, 2018)?

I think it can be achieved via a measure in Power Pivot and date table, but I am just quite new to DAX and time dimension concept overall.

My measure reads:

AvailUnitCount := CALCULATE( DISTINCTCOUNT( EquipUsage[EquipmentNo] ) )

How do I incorporate time dimension into the measure above, so I can report on available equipment for a specific date by moving a timeline in Excel?

Please see the data set and the desired outcome below. I immensely appreciate your advice on this.

Table 1: EquipUsage

EquipNo     CategoryNo  UsageStartDate  UsageEndDate
----------------------------------------------------
10005164    A020004004  5-Nov-18        5-Dec-18
10005167    A020004004  24-Oct-18       10-Nov-18
10005176    A020004005  9-Oct-18        5-Dec-18
10015982    A020004006  18-Feb-18       5-Sep-18
10019170    A020004006  16-Aug-18       30-Mar-19
10019551    A020004006  2-May-17        10-Nov-18
10005178    A020004007  20-Sep-18       15-Jan-19

Table 2: EquipCategories (Example of Desired Outcome for November 11, 2018)

CategoryNo  AllUnits    AvailableUnits
--------------------------------------
A020004004  2           1
A020004005  1           0
A020004006  3           2
A020004007  1           0

Why is 10005178 of category A020004007 available on that date? — Alexis Olson
@AlexisOlson good job on spotting this discrepancy. I have corrected it to 0. — Roman

Alexis Olson Alexis Olson · Accepted Answer · 2018-11-15T21:00:32

The AllUnits measure is simple:

AllUnits = COUNTROWS(EquipUsage)

For availability, read in your desired date and sum up the rows where that date does not fall in the given date range:

AvailableUnits = 
VAR CheckDate = SELECTEDVALUE(DateTable[Date])
RETURN SUMX(EquipUsage,
           IF(
               EquipUsage[UsageStartDate] <= CheckDate &&
               EquipUsage[UsageEndDate]   >= CheckDate,
               0,
               1
           )
       )

Create a Measure in Power Pivot / DAX that calculates available equipment based on date ranges

1 Answers