Forum Discussion
Date difference (separate columns in separate tables) based on a unique identifier
Hello everyone!
I have 2 tables: "applications" and "funding"
"applications" contains columns including a unique column of "application ID" and "application date"
"funding" contains columns including a unique column of "application ID" and "funding date"
I would like to have a KPI created that shows the average time-to-close so funding[funding date]-applications[application date] but based on the "application ID".
Not all applications will be funded.
Please let me know if you can help! I have tried at least 2 dozen different solutions searching the forums.
Hi TylerSimpson ,
Based on the information you provided I joined the Application and Funding tables on 'Application ID'
Then created the following column in application table (you can reverse it if you prefer it in the Funding table)
DateDiffCol = DATEDIFF(Applications[Application Date], RELATED(Funding[Funding Date]), DAY)
Then the following measure for the average Time to Close, which can be filtered to a specific Application ID.Time To Close = AVERAGE(Applications[DateDiffCol])
Below image to show an example:Hope this addresses your problem!
1 Reply
- johncolley
Solution Sage
Hi TylerSimpson ,
Based on the information you provided I joined the Application and Funding tables on 'Application ID'
Then created the following column in application table (you can reverse it if you prefer it in the Funding table)
DateDiffCol = DATEDIFF(Applications[Application Date], RELATED(Funding[Funding Date]), DAY)
Then the following measure for the average Time to Close, which can be filtered to a specific Application ID.Time To Close = AVERAGE(Applications[DateDiffCol])
Below image to show an example:Hope this addresses your problem!