Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dynamic Count

Hi all, I need one help for a logic to implement in my PBI I have date_open and date_close in my extract table I need to count active case count based on date_open and date_close The logic ...
Need Help
Tips and Tricks
MFelix's avatar
MFelix
Super User
2 years ago

So to what I can understand when the case is closed, on that specific moth still counts as open right?

 

Redo the measure to:

Active = 
COUNTROWS (
    FILTER (
        'Table',
        (
            'Table'[OPEN_DATE] <= MAX ( 'Calendar'[Date] )
                && EOMONTH('Table'[CLOSED_DATE],0) >= max ( 'Calendar'[Date] )
        )
            || (
                'Table'[OPEN_DATE] <= Max ( 'Calendar'[Date] )
                    && 'Table'[CLOSED_DATE] = BLANK ()
            )
    )
)

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

I will crealry exoplai

For example a matter is open in 28 april 2021, then it is closed on 5th may 2023

along with old matter keys it will add to older count like this

hope you understood

Then the matter_key for all these matters from may 2021 to may 2023
this will considered as active matter in each of the month after april 2021 till to may 2023

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Then the measure that I have send out should work,

     

    Because it's picking up the End of month and not the exact date of the close.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Active is calculated by [open_date < month_start and closed_date >= month_start] or [open_date < month_start and closed_date is blank] 
    it should pick start of month based on above logic

    can you please take a look

    Thanks in advance

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Try the following:

     

    Active = 
COUNTROWS (
    FILTER (
        'Table',
        (
           EOMONTH('Table'[OPEN_DATE],-1) + 1 <= MAX ( 'Calendar'[Date] )
                && EOMONTH('Table'[CLOSED_DATE],0) >= max ( 'Calendar'[Date] )
        )
            || (
                           EOMONTH('Table'[OPEN_DATE],-1) + 1 <= Max ( 'Calendar'[Date] )
                    && 'Table'[CLOSED_DATE] = BLANK ()
            )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    So thankful for the help,

    Here if I want to see the matter count which are closed in may 2023, then open date should be less than may 2023 ,

    Please help
    Thanks

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi Anonymous ,

     

    Try the following code:

    Closed = 
COUNTROWS (
    FILTER (
        'Table',
        (
            EOMONTH('Table'[OPEN_DATE], -1) + 1  <= MAX ( 'Calendar'[Date] )
                && EOMONTH('Table'[CLOSED_DATE],0)+1 <= MAX ( 'Calendar'[Date] )
        ) && 'Table'[CLOSED_DATE] <> BLANK()
            
    )
)