I need some input from the community as to a problem I’m conceptually struggling with. Seems like it pretty simple thing to solve, and I’ve tried various approaches to changing the filter context, but they’re all leading to expanded cross joins along the dates/property fields (and me banging my head against a wall).
Goal = define a baseline measure that shows the value of the [Site Energy Use (kBtu] where the [Energy Baseline] = True.
There are two slicers around the [RCH_Unique_ID]/[Property Name] and the Calendar ([Reporting Date]) and the baseline should show across all dates <= the last date being sliced.
The first six columns show the underlying data columns. The first measure is called [Energy Use kBtu] and defined correctly, showing the correct amounts when filtered.
[Energy Use kBtu Baseline] shows correctly only for the record where the baseline value is true ( [Energy Baseline]=True ). This is the value that should be returned for all dates <= [Last selected date].
I’ve tried various approaches to overriding the filter context, but as i mention these all have lead to including additional dates as part of the calculation.
Including an excel document with the underlying data above.
Energy Data.xlsx (11.6 KB)