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 for active case count is like below:
Active is calculated by [date_open < month_start and date_close >= month_start] or [date_open < month_start and date_closed is blank] 

For example if we need to see active case count for october
If any case that were opened before october 1st and closed after october 1st is considered as active case in october

please help

Thanks in advance

Need Help
Tips and Tricks

21 Replies

  • MFelix's avatar
    MFelix
    Super User
    2 years ago

    Hi Anonymous ,

     

    Not sure how the model is setup but you will need to have something similar to this:

    Active = COUNTROWS( FILTER( Table, (Table[date_open] <= Table[Month_Start] && Table[date_close] >=  Table [Month_start]) || (Table[date_open] <= Table[Month_Start] && Table[date_close] =   BLANK())

     

    Be aware this is a generic measure and needs to be adjusted to your model.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      i have 5 tables
      4 are fact and one is dim table ,
      all 4 fact to one dim relationship I have given, in many to one way
      Calendar table to dim table  open date many to one relation i given

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

       

      It's working fine whatever you provide

      Two things I need changes, 
      Cumulative values i want , for example if the matters closed in may 2023 will have open dates from 2016 to may 2023. 2016 will ahve one matter which have closed date in may 2023, then this 2016 may active matter should add to 2016 june and to all next months till the closing dates

      2nd change is, if matters closed date in may 2023, then open date should be till april 2023 no open dates in may

      Please reply back

      Thanks a lot

  • v-zhangti's avatar
    v-zhangti
    Community Support
    2 years ago

    Hi, Anonymous 

     

    Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

     

    Best Regards,

    Community Support Team _Charlotte

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Zhangti,

      MATTER_KEYOPEN_DATECLOSED_DATE
      4052106/28/1994  
      4392106/29/1994  


      I am just providing little data, this two matters are opened in 1994 but not closed till date, then it will be counted as active date till 2023, we need to take it in count for each month of each year till date

      Thanks in advance

      • MFelix's avatar
        MFelix
        Super User
        2 years ago

        Hi Anonymous ,

         

        I have added a new row to your data so we could have different results but see the measure below:

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

        You need to add a disconnected calendar table to your model:

        See file attach.