Count number of active cases over time based on date rangeundefined

[camgyl]

Hi

I'm trying to calculate the number of active cases over timer based on a date range between two dates; the date that the case was opened and the dates that the case was closed (if the case hasn't been closed yet the closed date will be blank and therefore the case is still open).

Example of the data:

ID           Open           Closed

1             1/1/2018     1/12/2018

2             1/4/2018     1/12/2018           

3             2/1/2018     2/14/2018

4             2/13/2018   2/13/2018

5             1/1/2018     1/14/2018

So on the 1/5/2018 there are 3 active cases. This is an example pr. day. For the week I want to see the average of open cases for the days included in that week. The same for the month and year.

I hope that someone has a brilliant solution to this. 

[v-easonf-msft]

Hi, @camgyl 

Create a visual control measure as below:

visual control =
VAR selected_date =
    SELECTEDVALUE ( 'Calendar Date'[Date] )
VAR flag =
    IF (
         (
            selected_date > SELECTEDVALUE ( 'Table'[Open] )
                && selected_date < SELECTEDVALUE ( 'Table'[Closed] )
        )
            || (
                selected_date > SELECTEDVALUE ( 'Table'[Open] )
                    && SELECTEDVALUE ( 'Table'[Closed] ) = BLANK ()
            ),
        1,
        0
    )
RETURN
    IF ( ISFILTERED ( 'Calendar Date'[Date] ), flag, 1 )

Them apply it to table visual filter pane :

Create measure "count" then apply it to a card visual:

Count =
VAR selected_date =
    SELECTEDVALUE ( 'Calendar Date'[Date] )
VAR tab =
    SUMMARIZE (
        FILTER (
            ALL ( 'Table' ),
             ( selected_date > 'Table'[Open]
                && selected_date < 'Table'[Closed] )
                || (
                    selected_date > 'Table'[Open]
                        && 'Table'[Closed] = BLANK ()
                )
        ),
        'Table'[ID]
    )
RETURN
    COUNTX ( tab, [ID] )

The result will show as below:

Please check attached pbix file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@camgyl , Please refer to my HR blog on a similar topic -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or video -https://www.youtube.com/watch?v=e6Y-l_JtCq4