Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
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