Measure to aggregate a count of objects meeting two criteria

[SteveM123]

Hi,

I have a Calendar table with a dates field and an Objects table, the Objects table includes a column with a 'born on date' and a status field for 'active' or 'inactive'. I would like a measure that cycles through every Calendar date within a time span selected by a slicer and aggregates a count of units that have a 'born on date' falling within that selected time span and that also has a Object status = "active"

Thanks!

[SteveM123]

Thanks Zhu, I just tried your proposed measure and it does work, except for the case when a user applies a slicer on the pivot table against other fields in the Objects table in order to limit the population of Objects to be considered, for example by its geographic location.

Is there a way to modify this measure so that the context of filters applied on the pivot chart against the Objects table are also considered?

Thanks again!

[Anonymous · Accepted solution]

Thanks for the reply from FreemanZ.

Hi @SteveM123 ,

Based on your description, l created simple test data:

Create a measure:

Measure = 
VAR _startdate=MIN('Calendar'[Date])
VAR _enddate=MAX('Calendar'[Date])
VAR _interval=DATEDIFF(_startdate,_enddate,DAY)
RETURN
IF(MAX('Objects'[Start Date])<=_enddate&&OR(MAX('Objects'[End Date])>_startdate,MAX('Objects'[End Date])=BLANK()),DIVIDE(CALCULATE(COUNTROWS('Objects')),_interval))

The result is as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[SteveM123]

Hi,

Thanks for that reply, but in my attempt to be succinct, I made a mistake in describing the scenario. A unit can be currently active, or it can be currently  inactive, but even a unit that is inactive today, was active at some earlier point in time. As such, my Objects table has both a 'born on date', call it a 'start date' and a 'decommissioned date' , lets call it an 'end date'

So I should have stated that the goal is to aggregate the number of Objects in the Objects table that were active for the slicer selected period, including objects that may be inactive today, but were active back during the period selected by the slicer.

So perhaps I need a measure that cycles through all calendar dates within the slicer selected period and for each of those dates, loops through the Objects table and aggregates a count of objects in the object table that have a 'start date' before the calendar date and either no 'end date' or an 'end date that is later than the current calendar date being evaluated. I would then divide that aggregated number by the number of days in the slicer selected peroid to obtain an effective count of operating units for the period which may or may not be a whole number

[FreemanZ]

hi @SteveM123 ,

try to:

1. relate the calendar table and objects table.

2. plot the slicer with calendar[date]

3.plot a measure like:

measure =

COUNTROWS(

  FILTER(objects, objects[status]="active")

)