Power BI M or DAX - Combine results of subquery (or measure) in aggregate with each row of a table, then roll up by date

Question

I am looking for a way to roll up aggregates over time of a subquery and a time series in either DAX or M. The solutions I can think of are a measure applied per row in an aggregate in DAX, or join on a subquery expression in M, but I cannot find the functions to do this in the documentation.

An example SQL Solution:

SELECT
    t1.Date,
    t1.Month,
    DailyOccupancy - Capacity 'Availability',
    r.Rate,
    (DailyOccupancy - Capacity) * r.Rate 'OverUnderCost'
FROM
    Rate r 
    INNER JOIN
(SELECT 
    d.Date,
    d.Month,
    SUM(c.Capacity) 'DailyCapacity',
    SUM(o.Occupancy) 'DailyOccupancy'
FROM
    DimDate d
    INNER JOIN Capacity c ON d.Date > c.StartDate AND d.Date < c.EndDate
    INNER JOIN Occupancy o ON d.Date = o.Date
GROUP BY
    d.Date,
    d.Month,
    r.Rate
) t1 ON o.Date = t1.Date

DAX Measures (works at the individual time step, but when using Capacity measure and a month worth of Occupancy, I get a single Capacity value and 30 Occupancy values summed)

DailyCapacity =
VAR currentDate = MAX ( DimDate[Date] )
RETURN
CALCULATE ( SUM('Capacity'[Capacity]),
FILTER ( 'Capacity',
( 'Capacity'[StartDate] <= currentDate
&& 'Capacity'[EndDate] >= currentDate )))

Available =  CALCULATE(SUM('Occupancy'[Occupancy]) - ('Capacity'[DailyCapacity ]))
(Works for each individual time step, doesn't roll up over time)

Is there a DAX method to sum the Capacity measure minus the Available aggregate for each timestep?

Another approach is to use M to create a "DailyCapacity" table. In SQL:

SELECT 
    d.Date,
    SUM(c.Capacity) 'DailyCapacity',
FROM
    DimDate d
    INNER JOIN Capacity c ON d.Date > c.StartDate AND d.Date < c.EndDate
GROUP BY
    d.Date

but I can't find a way to join on a boolean expression in M, only keys. Is there a way to join on a boolean expression, not keys in M?

My tables:

DimDate (Date, day, Month, Year, Billing Month, FY)
----------
1/1/2019, 1, 1, 2019, 1, 2019
1/2/2019, 2, 1, 2019, 1, 2019
... Every time step possible


Capacity (StartDate, EndDate, Capacity, Notes)
----------
1/1/2019, 12/31/2019, 40, "Annual Cap"
6/1/2019, 9/15/2019, 30, "Summer Peak"

Occupancy (Date, Occupancy, Location)
----------
1/1/2019, 20, 1
1/1/2019, 17, 2
1/2/2019, 30, 1
1/2/2019, 9, 2
1/3/2019, 22, 1
1/3/2019, 20, 2

Rate (Date, Rate)
----------
1/1/2019, $49.99
1/2/2019, $64.99
... etc.

Needed Output

Available Space:
----------
1/1/2019, 3
1/2/2019, 1
1/3/2019, -2
1/4/2019, 0

And aggregates on rolled up time steps:

SUM(Available Space * Current Day's Rate) per month, quarter, year, etc. Including over and under capacity totals as negative and positive.

Alexis Olson Alexis Olson · Accepted Answer · 2019-10-25T17:15:10

To do this in DAX, let's write a DailyCapacity measure (very similar to yours) that we'll use in another measure. (I use SELECTEDVALUE here so it's more obvious if I'm dealing with more than one value but your MAX and SUM should work too.)

DailyCapacity =
VAR currentDate = SELECTEDVALUE ( DimDate[Date] )
RETURN
    CALCULATE (
        SELECTEDVALUE ( Capacity[Capacity] ),
        FILTER (
            Capacity,
            Capacity[StartDate] <= currentDate &&
            Capacity[EndDate] >= currentDate
        )
    )

Now to calculated available space, we'll iterate through each row of Occupancy and call the measure we just defined (assuming your DimDate table has a relationship that filters Occupancy).

AvailableSpace = SUMX ( Occupancy, [DailyCapacity] - Occupancy[Occupancy] )

This measure should roll up as expected since it's computing the daily capacity for each row, not just once.

Similarly, if you write a measure for DailyRate like you did for DailyCapacity, then you can write an opportunity cost measure SUMX ( DimDate, [AvailableSpace] * [DailyRate] ).

Power BI M or DAX - Combine results of subquery (or measure) in aggregate with each row of a table, then roll up by date

1 Answers