Measure to count items active during a specific month

[BG919]

I am trying to write a measure that counts all items that were active in a given month, even if they are closed now. 

UniqueID	ReportedDate	ClosedDate	WorkflowState
1	1/5/2023	1/12/2023	Closed
2	1/7/2023		Active
3	1/14/2023	2/9/2023	Closed
4	2/9/2023	3/24/2023	Closed
5	3/4/2023		Active
6	3/12/2023	5/15/2023	Closed
7	4/2/2023	5/15/2023	Closed
8	4/5/2023	4/18/2023	Closed
9	4/9/2023		Active

The output I am looking for based on this sample data:

• The January count would be 3
  • ID 2 that is still active
  • ID 1 that is closed but was active during January
  • ID 3 that closed after January's completion
• The February count would be 3
  • ID 2 that is still active.
  • ID 3 that closed in February but was active at some point during the month.
  • ID 4 opened in February but closed after the month's completion.
• The March count would be 4
  • ID 2 and 5 that are still active
  • ID 4 that closed in March but was active during the month
  • ID 6 that closed after March's completion.
• The April count would be 6: 
  • ID 2, 5, and 9 that are still active
  • ID 8 that closed in April but was active during the month
  • ID 6 and 7 that closed after April's completion.

The ultimate intent is to plot this on a line and clustered column chart aggregated at the year-month level - the line value trending how many total items were active from month-to-month, the columns showing how many items were opened or closed during that month. I already have the measures to do the bar values.

Thanks in advance for your input!

[gmsamborn · Accepted solution]

Hi @BG919 

Would a measure like this help?

Active = 
VAR _Curr = MAX( 'Date'[Date] )
VAR _EndOfMonth = EOMONTH( _Curr, 0 )
VAR _StartOfMonth = EOMONTH( _Curr, -1 ) + 1
VAR _Count =
    COUNTROWS(
        FILTER(
            ALLSELECTED( 'Table'[ReportedDate], 'Table'[ClosedDate] ),
            'Table'[ReportedDate] < _EndOfMonth
                && OR(
                        'Table'[ClosedDate] = BLANK(),
                        'Table'[ClosedDate] > _StartOfMonth
                )
        )
    )
RETURN
    _Count

Active during month.pbix

[gmsamborn · Accepted solution]

Hi @BG919 

Would a measure like this help?

Active = 
VAR _Curr = MAX( 'Date'[Date] )
VAR _EndOfMonth = EOMONTH( _Curr, 0 )
VAR _StartOfMonth = EOMONTH( _Curr, -1 ) + 1
VAR _Count =
    COUNTROWS(
        FILTER(
            ALLSELECTED( 'Table'[ReportedDate], 'Table'[ClosedDate] ),
            'Table'[ReportedDate] < _EndOfMonth
                && OR(
                        'Table'[ClosedDate] = BLANK(),
                        'Table'[ClosedDate] > _StartOfMonth
                )
        )
    )
RETURN
    _Count

Active during month.pbix

[BG919]

The measure worked on the sample dataset, but broke down when I applied it on my full dataset. I was able to figure out where it was going wrong, but was not able to adjust the measure to account for it.

The issue comes up when you have two records with the same open date and same close date. So if we added a 10th row that had a report date of 1/5/2023 and close date of 1/12/2023 (same as ID 1), my January count would still show as 3 rather than 4; the measure is treating those two as if they are the same record.

[gmsamborn · Accepted solution]

Hi @BG919 

Sorry about that.

Try this:

Active = 
VAR _Curr = MAX( 'Date'[Date] )
VAR _EndOfMonth = EOMONTH( _Curr, 0 )
VAR _StartOfMonth = EOMONTH( _Curr, -1 ) + 1
VAR _Count =
    COUNTROWS(
        FILTER(
            ALLSELECTED( 
                'Table'[ReportedDate], 
                'Table'[ClosedDate],
                'Table'[UniqueID]
            ),
            'Table'[ReportedDate] < _EndOfMonth
                && OR(
                        'Table'[ClosedDate] = BLANK(),
                        'Table'[ClosedDate] > _StartOfMonth
                )
        )
    )
RETURN
    _Count

Let me know if that helps.

[TomMartens]

Hey @BG919 ,

the challenge you are facing is called event-in-progress. This article "https://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/" by Gerhard Brueckl references all the relevant articles you need to know to tackle your challenge. Start with the one by Jason Thomas.

Regards,

Tom