Need aggregate of subquery and row values in Power BI

[mr_fnord]

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.

[amitchandak]

Trying moving max date inside the calculate. If it does not work, if possible please share a sample pbix file after removing sensitive information.

DailyCapacity =

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

Available =  CALCULATE(SUM('Occupancy'[Occupancy]) - ('Capacity'[DailyCapacity ]))

Thanks

[lc_finance]

Hi @mr_fnord ,

could you share a sample Power BI file?

That will make it easier to help you.

You can use One Drive, Google Drive or another similar tool to share your file.

Regards,

LC