Need help with a measure for data at the start of a given period

[jbranch]

I am attempting to build a gains and losses report for my company that can be filtered by month. It needs to list each site that we have, their status at the beginning or end of the month, and whether or not that changed during the month. Sites are either commissioned or decomissioned, but a site that is decomissioned can be recomissioned, because of this our database has a seperate log for these changes. I'm currently stuck trying to make the measure I need for the sites Brought Forward.

I need to see the number of sites that were commissioned at the start of the selected month (currently selected through a slicer on the Month table, linked only through the measure itself), so I need to pull the most recent commissioning time before the start of the month. To do this I've written the following:

VAR st = MAX('month'[Start])

VAR recent = FILTER(Commissioning, Commissioning[evtime] < st)

VAR newest = CALCULATE(MAX(Commissioning[evtime]),recent)

This does correctly pull the most recent date before the start of the month, but I can't quite figure out how to pull whether it was a commission or decommission, and then perform a count on top of that. The following pulls the commissioning status properly:

VAR stat = CALCULATE(MAX(Commissioning[evqual]), Commissioning[evtime] = newest)

Every attempt I have made returns an error, either not being able to use a boolean, or being unable to turn two columns into a scalar value. In essense I just want everything where stat = "Commissioned". Would anyone be able to point me in the right direction with this?

[rajendraongole1 · Accepted solution]

Hi @jbranch  - very close, can you try the below logic:

Sites Brought Forward =
VAR st = MAX('Month'[Start]) -- selected month's start

-- Step 1: filter events BEFORE selected month
VAR EventsBeforeMonth =
FILTER (
Commissioning,
Commissioning[evtime] < st
)

-- Step 2: get the latest event per Site before the month
VAR LatestPerSite =
ADDCOLUMNS (
SUMMARIZE (EventsBeforeMonth, Commissioning[SiteID]),
"LatestTime", CALCULATE (
MAX (Commissioning[evtime]),
FILTER (
EventsBeforeMonth,
Commissioning[SiteID] = EARLIER (Commissioning[SiteID])
)
)
)

-- Step 3: bring in the corresponding status for that event
VAR WithStatus =
ADDCOLUMNS (
LatestPerSite,
"Status", CALCULATE (
MAX (Commissioning[evqual]),
FILTER (
Commissioning,
Commissioning[SiteID] = EARLIER (Commissioning[SiteID]) &&
Commissioning[evtime] = [LatestTime]
)
)
)

-- Step 4: count how many had "Commissioned" as latest status
RETURN
COUNTROWS (
FILTER (WithStatus, [Status] = "Commissioned")
)

Hope this measure will return the number of sites that were last set to “Commissioned” before the selected month, i.e. the correct "Brought Forward" value.

[jbranch]

Hi @rajendraongole1 

Thank you for your help, that works perfectly.