Count the number of "in progress" applications with IN and a OUT dates in a calendar table

[des_san]

Hi all,

I have a table that looks like this:

Application ID	Date Created	Date Resolved	Status
001	01/01/2019	01/05/2019	Approved
002	01/03/2019	01/07/2019	Rejected
003	01/09/2019		Pending

I would like to create a table that counts the number of applications that are in progress for each date, alongside a count of applications created or resolved on that date. For example:

Calendar Date	Created	Resolved	In Progress
01/01/2019	1	0	0
01/02/2019	0	0	1
01/03/2019	1	0	1
01/04/2019	0	0	2
01/05/2019	0	1	1
01/06/2019	0	0	1
01/07/2019	0	1	0
01/08/2019	0	0	0
01/09/2019	1	0	0

Would you have a solution?

[v-piga-msft]

Hi @des_san ,

You could create the two calculated column like below in calendar table to achieve the Created and Resolved. And then create the measure with the dax below.

Created =
IF (
    LOOKUPVALUE ( 'Table'[Date Created], 'Table'[Date Created], 'calendar'[Date] ) > 0,
    1,
    0
)
Resloved =
IF (
    LOOKUPVALUE ( 'Table'[Date Resolved], 'Table'[Date Resolved], 'calendar'[Date] ) > 0,
    1,
    0
)
In Progress =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        'Table',
        'Table'[Date Created] < MAX ( 'calendar'[Date] )
            && 'Table'[Date Resolved] > MAX ( 'calendar'[Date] )
    )
)

Here is the output.

Best Regards,

Cherry

[des_san]

Hi @v-piga-msft ,

Thank you, your response is very helpful. However, I noticed that if there are multiple created/resolved applications on a particular day the calculated columns aren't accounting for them. I'm only getting 1. Do you have any ideas that might help count for multiple apps in a day?

Best,

[v-piga-msft]

Hi @des_san ,

I'm a little confused about your requirement.

If it is convenient, could you describe your requirement in details and share the output you desired so that we could help further on it.

Best Regards,

Cherry

[des_san · Accepted solution]

Hi @v-piga-msft ,

I figured it out. Thanks again for pointing me in the right direction.

Solution:

In the Date table I created the following measures and columns:

Created Count = CALCULATE(COUNT('Table'[CreatedDate]),FILTER('Table','Table'[CreatedDate ]='Date'[Date]))

Resolved Count = CALCULATE(COUNT('Table'[Date_Resolved]),FILTER(Table','Table'[Date_Resolved]='Date'[Date]))

In Progress = 
CALCULATE(
    COUNT('Table'[CreatedDate]),
    FILTER(
        'Table',
        'Table'[CreatedDate]<'Date'[Date] &&
        'Table'[Date_Resolved]>'Date'[Date]
    )
) 

The final result as a table is as desired:

Date	Created Count	Resolved Count	In Progress
7/31/2019	193	515	15,904
7/30/2019	225	579	16,195
7/29/2019	195	530	16,578
7/28/2019	148	40	16,960

Cheers,