Forum Discussion

BG919's avatar
BG919
Frequent Visitor
2 years ago
Solved

Measure to count items active during a specific month

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/202...
Need Help
  • gmsamborn's avatar
    gmsamborn
    2 years ago

    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's avatar
    gmsamborn
    2 years ago

    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.

BG919's avatar
BG919
Frequent Visitor
2 years ago

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's avatar
gmsamborn
Icon for Super User rankSuper User
2 years ago

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.