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.