Count of open files

[Anonymous]

Hello,

I have a report in excel that I would like to convert to PowerBI.  I have a data set with one column of open dates and another of close dates.  I would like to create a graph, as below, which counts the number of files that have calendar dates that fall between an open and close date.

The purpose of the report is show how many open files there are at any given date as close dates for open files at set as 1-1-2099.

[v-cherch-msft]

Hi @Anonymous

You may set the relationship with calendar table and fact table. Then create the visual as requested. Show a simplified sample as below. If it is not your case, please share some data sample and expected output for your scenario so that we could help further on it.

Regards,

Cherie

[Anonymous]

Hello,

Thank you for the reply.  I essentially need each calendar day to calculate how many files have a receive date greater than the calendar date in question and less than the final_action_date (IE close date)

I have included some sample data below with some PMI data excluded.  The count underneath the calendar dates are a product of the following calculation.  The calculation provided is in the first cell underneath 10/18/2018.

IF(AND(I$15>=$D16,I$15<=IF($E16="",$G$14,$E16)),1,0)

I15 = Calendar date of 10/18/2018

D16 = Receive_date (IE Open Date)

E16 = Final_Action_date (IE Close date)

The graph created by this data is in my first post.

association_name	g_number	case_number	receive_date	final_action_date	BusType	Error		10/18/2018	10/19/2018	10/20/2018	10/21/2018	10/22/2018	10/23/2018
			18-Oct-18	27-Oct-18	Other			1	1	1	1	1	1
			18-Oct-18	22-Oct-18	Other			1	1	1	1	1	0
			18-Oct-18	23-Oct-18	Other			1	1	1	1	1	1
			18-Oct-18	19-Oct-18	Other			1	1	0	0	0	0
			18-Oct-18	22-Oct-18	Other			1	1	1	1	1	0
			18-Oct-18	22-Oct-18	Other			1	1	1	1	1	0
			18-Oct-18	23-Oct-18	Other			1	1	1	1	1	1
			18-Oct-18	23-Oct-18	Other			1	1	1	1	1	1
			18-Oct-18	24-Oct-18	Other			1	1	1	1	1	1
			18-Oct-18	23-Oct-18	Other			1	1	1	1	1	1

[Anonymous · Accepted solution]

I just wanted to let you know I was able to get this to work using the following formula.

Measure = AVERAGEX (
    VALUES ( 'tbl_DATE_INFO'[DB_DATE] ),
    VAR CurrentDate = 'tbl_DATE_INFO'[DB_DATE]
    VAR ReceivedBeforeCurrentDate =
        FILTER (
            ALL ( 'rp_all_applicants'[receive_date] ),
            rp_all_applicants[receive_date] <= CurrentDate
        )
    VAR ClosedAfterCurrentDate =
        FILTER (
            ALL ( 'rp_all_applicants'[final_action_date] ),
            rp_all_applicants[final_action_date] >= CurrentDate
        )
    RETURN
        CALCULATE (
            COUNTROWS ( rp_all_applicants ),
            ReceivedBeforeCurrentDate,
            ClosedAfterCurrentDate,
            ALL ( 'tbl_DATE_INFO' )
        )
)