Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
92 | |
82 | |
63 | |
62 | |
58 |
User | Count |
---|---|
159 | |
114 | |
100 | |
75 | |
65 |