Need to calculate number of active cases at any date

[Claus_Vad]

Hi there
I need to calculate number of active cases at any date, and show them in a line chart

Have a fact table with around 2,500,000 records/cases, dates mostly opening days monday - friday, but want to show for all days

Every case has a start date and a end date (if the case is closed)

Any sugestions?

Regards Claus

[v-cazheng-msft · Accepted solution]

Hi @Claus_Vad,

You need a calendar table firstly, and you could create it by a Calculated table.

Calendar =
VAR minDate =
    MIN ( 'Fact Table'[start date] )
VAR maxDate =
    TODAY ()
RETURN
    CALENDAR ( minDate, maxDate )

Then, you need a Measure to count cases.

Active Case Number =
VAR num =
    CALCULATE (
        COUNT ( 'Fact Table'[case] ),
        FILTER (
            'Fact Table',
            'Fact Table'[start date] <= MAX ( 'Calendar'[Date] )
                && ISBLANK ( 'Fact Table'[end date] )
        )
    )
RETURN
    IF ( ISBLANK ( num ), 0, num )

The result looks like this.

Also, attached the pbix file.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,                                             

Community Support Team _ Caiyun

[tamerj1]

Hi @Claus_Vad 
Please define your business logic of "active cases". Thank you

[Claus_Vad]

Hi @tamerj1

a case is active when it have a startdate and enddate is blank

[v-cazheng-msft · Accepted solution]

Hi @Claus_Vad,

You need a calendar table firstly, and you could create it by a Calculated table.

Calendar =
VAR minDate =
    MIN ( 'Fact Table'[start date] )
VAR maxDate =
    TODAY ()
RETURN
    CALENDAR ( minDate, maxDate )

Then, you need a Measure to count cases.

Active Case Number =
VAR num =
    CALCULATE (
        COUNT ( 'Fact Table'[case] ),
        FILTER (
            'Fact Table',
            'Fact Table'[start date] <= MAX ( 'Calendar'[Date] )
                && ISBLANK ( 'Fact Table'[end date] )
        )
    )
RETURN
    IF ( ISBLANK ( num ), 0, num )

The result looks like this.

Also, attached the pbix file.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,                                             

Community Support Team _ Caiyun

[Claus_Vad · Accepted solution]

Hi @v-cazheng-msft 

Thanks for your sugestion 

It was almost right, this will do what I want 

Active Case Number =

VAR num =

CALCULATE (

COUNT ( 'Fact Table'[case] ),

FILTER (

'Fact Table',

'Fact Table'[start date] <= MAX ( 'Calendar'[Date] )

&& ('Fact Table'[end date] > min('Calendar'[Date])

|| ISBLANK ( 'Fact Table'[end date] ))

)

)

RETURN

IF ( ISBLANK ( num ), 0, num )