Dynamic Count

[Anonymous]

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

[v-zhangti]

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]

Hi Zhangti,

MATTER_KEY	OPEN_DATE	CLOSED_DATE
40521	06/28/1994
43921	06/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]

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.

[Anonymous]

Hi, can you please let me know how yoy have added only 1994 to 2023 years in calendar tabkle
beacuse when i am using same formula creating in my pbix file by using your calendar formula it's creating from 1992 to 2121, i don't know why

please help
Thanks

[MFelix]

I'm using the CALENDARAUTO this picks up the min date on your dataset and the max date to get the first and last year if you want to close down the number of years try the following code instead of the calendarauto.:

CALENDAR("01/01/1994", DATE(YEAR(TODAY()),12, 31))

[Anonymous]

Hi ,
I am not getting correct count actually,
I'll share the snip of my pbix example output for may 2023 active matters
Active Data 2023 May.csv

So here the open dates below may 1st and closed date in may 

[MFelix]

There is no information attached.

[MFelix]

Hi @Anonymous ,

Is this Active measure that I sent to you? Are you abble to share the PBIX file so I can check it if possible.

[Anonymous]

Hello,

that's a manual data i pasted here from my data

From your pbix file, i have doubt that, the matters count for j2023 should be 3, because 1994 matters are not closed yet so count for them is 2 and matter in 2022 is closed in 2023 jan 5, so after 1st oif jan then it's considered as active matter for jan
so total 3 for year 2023 I guess,
please let me know on this

[MFelix]

Hi @Anonymous ,

This depends on the context, when you look at 2023 overall the ones that are still open are the ones that started in 1994, at the end of the year the other one was closed in 5 th of january so it's closed.

Depending on the level of aggregation you are looking at the value is 2 or 3 even in 2023.

[Anonymous]

so as per your data i want to count 3 in jan 2023
and in my logic if you see, open date should only be less than month_start not <=
can you please let me know exactly how to implement this please


Thanks in advance,

[MFelix]

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]

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]

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]

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]

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]

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]

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()
            
    )
)

[Anonymous]

ActiveNew	OPEN_DATE	CLOSED_DATE
1	3/25/2020	5/1/2023
1	6/7/2022	5/1/2023
2	7/7/2022	5/1/2023
1	8/12/2022	5/1/2023
1	8/25/2022	5/1/2023
2	9/6/2022	5/1/2023
1	9/9/2022	5/1/2023
2	9/12/2022	5/1/2023
1	9/13/2022	5/1/2023
1	9/21/2022	5/1/2023
2	10/5/2022	5/1/2023
1	11/8/2022	5/1/2023
1	11/18/2022	5/1/2023
1	12/2/2022	5/1/2023
1	12/9/2022	5/1/2023
1	1/20/2023	5/1/2023
1	2/7/2023	5/1/2023
1	3/21/2023	5/1/2023
2	3/28/2023	5/1/2023
1	4/13/2023	5/1/2023
1	4/20/2023	5/1/2023
1	7/23/2018	5/2/2023
1	3/13/2020	5/2/2023
1	9/22/2022	5/2/2023
1	9/26/2022	5/2/2023
1	10/10/2022	5/2/2023
3	10/13/2022	5/2/2023
1	10/18/2022	5/2/2023
1	10/21/2022	5/2/2023
1	10/23/2022	5/2/2023
1	10/27/2022	5/2/2023
1	10/28/2022	5/2/2023
2	10/31/2022	5/2/2023
2	11/2/2022	5/2/2023
2	11/3/2022	5/2/2023
2	11/4/2022	5/2/2023
1	11/10/2022	5/2/2023
1	11/11/2022	5/2/2023
1	11/13/2022	5/2/2023
2	11/17/2022	5/2/2023
1	11/23/2022	5/2/2023
1	11/27/2022	5/2/2023
1	11/28/2022	5/2/2023
1	12/1/2022	5/2/2023
1	12/6/2022	5/2/2023
1	12/18/2022	5/2/2023
1	1/19/2023	5/2/2023
1	1/23/2023	5/2/2023
2	1/28/2023	5/2/2023
1	2/13/2023	5/2/2023
1	2/27/2023	5/2/2023
1	3/3/2023	5/2/2023
1	3/23/2023	5/2/2023
1	3/24/2023	5/2/2023

[MFelix]

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.