Forum Discussion
Count of open files
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' )
)
)
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 |
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' )
)
)