Count cases with open status on selected periods

[AsnateKalnina]

Hi,

I prepared Excel file with sample dataset, manual calculations with Excel formulas and the desired result. Please see screen shot of the first rows of the table.

Part of the table itself is at the bottom.

In Power Bi I have a separate dimension table for general Calendar dates.

And I have DAX expression to calculate the desiderd result:

Count in Period =

CALCULATE (

    DISTINCTCOUNT( 'Fact'[Case Number]),

    FILTER (

        'Dim Calendar',

        MAX('Dim Calendar'[Date]) >= MIN( 'Fact'[D_VALID_FROM] )

            && MIN('Dim Calendar'[Date]) <= MAX ( 'Fact'[D_VALID_TO])

    )

)

It seems that the DAX formula is calculating correcly, but I do not quite understand how to link the desired result to the Calendar.

I can not link the Calendar table (Date) to D_VALID_FROM or D_VALID_TO fields.

If there are several weeks in between those dates, and user selects one of those interim weeks, the particular Case Number would not show in the result. 

But the overall process and problem is this:

I have a list of Case Numbers with their respective validity periods from- to.

Case Numbers may repeat as periods differ. 

The periods for one Case Number can be continuous- one period immediately follows the previous period, or a Case Number can have a gap in between two separate periods. 

At the end I want to have a chart with all week numbers on X axis (and week numbers can be filtered by normal Calendar table), and count number of cases- how many were valid in that week (or generally- selected period).

In the Excel file I highlight Case Numbers that have several lines,

If these periods overlap, they should not be counted as 2 cases per that week, but only as 1 case for the particular week.

So I erased the unnecessary results and marked those cells red.

Initially, it would be enough to have the result on weekly basis. So user can select one or several weeks in Calendar table and see number of active Case Numbers on that week, and also the specific case numbers that were active on that period.

Ideally, the calculation would work also for other manually selected periods of time, e.g. specific date periods. 

If there are any discussions about similar problems with solutions, I would appreciate if you could share those, as I have not managed to find yet.

This is the desired output ->	Week Number	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40	41	42	43	44	45	46	47	48	49	50	51	52	1	2	3	4	5	6
Count	13	12	13	16	15	13	9	7	14	23	23	22	21	23	21	21	19	18	20	14	16	15	12	13	14	15	16	12	11	11	9	10	10	11	11	11
			week start	06.06.2022	13.06.2022	20.06.2022	27.06.2022	04.07.2022	11.07.2022	18.07.2022	25.07.2022	01.08.2022	08.08.2022	15.08.2022	22.08.2022	29.08.2022	05.09.2022	12.09.2022	19.09.2022	26.09.2022	03.10.2022	10.10.2022	17.10.2022	24.10.2022	31.10.2022	07.11.2022	14.11.2022	21.11.2022	28.11.2022	05.12.2022	12.12.2022	19.12.2022	26.12.2022	02.01.2023	09.01.2023	16.01.2023	23.01.2023	30.01.2023	06.02.2023
these three columns are the initial dataset	week end	12.06.2022	19.06.2022	26.06.2022	03.07.2022	10.07.2022	17.07.2022	24.07.2022	31.07.2022	07.08.2022	14.08.2022	21.08.2022	28.08.2022	04.09.2022	11.09.2022	18.09.2022	25.09.2022	02.10.2022	09.10.2022	16.10.2022	23.10.2022	30.10.2022	06.11.2022	13.11.2022	20.11.2022	27.11.2022	04.12.2022	11.12.2022	18.12.2022	25.12.2022	01.01.2023	08.01.2023	15.01.2023	22.01.2023	29.01.2023	05.02.2023	12.02.2023
D_VALID_FROM	D_VALID_TO	Case Number	duplicate
08.08.2022	07.09.2022	105	2	0	0	0	0	0	0	0	0	0	1	1	1	1		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
08.09.2022	08.09.2022	105	2	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
08.06.2022	03.07.2022	3883	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
17.10.2022	17.10.2022	11156	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
18.10.2022	24.10.2022	11156	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
08.08.2022	25.08.2022	12129	3	0	0	0	0	0	0	0	0	0	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
07.09.2022	07.09.2022	12129	3	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
08.09.2022	08.09.2022	12129	3	0	0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
08.06.2022	31.12.2999	14072	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
30.06.2022	07.07.2022	16529	2	0	0	0	1		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
08.07.2022	12.07.2022	16529	2	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
14.12.2022	27.12.2022	374230	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1		0	0	0	0	0	0
28.12.2022	28.12.2022	374230	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0
29.12.2022	29.12.2022	374230	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
08.06.2022	21.07.2022	16679	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
08.06.2022	21.07.2022	16696	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
30.09.2022	12.10.2022	20210415	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
08.09.2022	08.09.2022	20210415	2	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
08.06.2022	12.10.2022	16697	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
03.08.2022	22.09.2022	16812	1	0	0	0	0	0	0	0	0	1	1	1	1	1	1	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

[OwenAuger · Accepted solution]

Hi there @AsnateKalnina 

Take a look at this article that covers this type of "events in progress" calculation:

https://www.daxpatterns.com/events-in-progress/

Your measure is following the right sort of logic, but needs to be written a bit differently. I have created a sample PBIX using your data above.

In my dataset, there is no relationship between 'Dim Date' and 'Fact'. 

Case Count = 
VAR MinDate =
    MIN ( 'Dim Calendar'[Date] )
VAR MaxDate =
    MAX ( 'Dim Calendar'[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( 'Fact'[Case Number] ),
        'Fact'[D_VALID_FROM] <= MaxDate,
        'Fact'[D_VALID_TO] >= MinDate
    )
RETURN
    Result

If there is a relationship between one of the date columns of 'Fact' and 'Dim Date' in your model:

• You would need to add REMOVEFILTETRS ( 'Dim Date' ) within CALCULATE.
• You would also want to create a Case Number dimension if Case Number is to be shown in the visuals

In my model, my Week Numbers are off by one compared to yours, but illustrate the idea.

Does something like this work in your model?

Regards,

Owen

[AsnateKalnina]

Yes, this works perfectly! Thank you very much!