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

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