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 see 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,
o.Occupancy - Capacity 'Availability',
r.Rate,
(o.Occupancy - Capacity) * r.Rate 'OverUnderCost'
FROM
Occupancy o
INNER JOIN Rate r ON o.Date = r.Date
INNER JOIN
(SELECT
d.Date,
d.Month,
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,
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)
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.
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)
----------
1/1/2019, 37
1/2/2019, 39
1/3/2019, 42
1/4/2019, 40
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.
