Forum Discussion
Calculating Completion Percent Based on Multiple Date Fields
Hello,
I will do my best to explain what I am trying to calculate. My data set will look something like the following:
| Line Enter Date | Line Data Date | Line Complete Date |
| 4/5/2019 | 4/5/2019 | 4/9/2019 |
| 4/7/2019 | 4/8/2019 | 4/12/2019 |
| 4/8/2019 | 4/10/2019 | |
| 4/8/2019 | 4/9/2019 | |
| 4/10/2019 | 4/10/2019 | |
| 4/10/2019 | 4/10/2019 | |
| 4/12/2019 | 4/14/2019 | 4/14/2019 |
| 4/14/2019 | ||
| 4/14/2019 | 4/14/2019 |
I am trying to calculate the percentage of Open Lines that contain data (where Open Line is defined as an entered line with no Complete Date).
The table below represents the logic for this sample data and the desired result in the far right column:
| Calendar Date | New Lines Entered | New Data Entered | Lines Completed | Lines with Data Completed | Total Open Lines | Total Open Lines with Data | Data Percentage |
| 4/5/2019 | 1 | 1 | 0 | 0 | 1 | 1 | 100% |
| 4/6/2019 | 0 | 0 | 0 | 0 | 1 | 1 | 100% |
| 4/7/2019 | 1 | 0 | 0 | 0 | 2 | 1 | 50% |
| 4/8/2019 | 2 | 1 | 0 | 0 | 4 | 2 | 50% |
| 4/9/2019 | 0 | 1 | 1 | 1 | 3 | 2 | 67% |
| 4/10/2019 | 2 | 2 | 1 | 0 | 4 | 4 | 100% |
| 4/11/2019 | 0 | 0 | 0 | 0 | 4 | 4 | 100% |
| 4/12/2019 | 1 | 0 | 1 | 1 | 4 | 3 | 75% |
| 4/13/2019 | 0 | 0 | 0 | 0 | 4 | 3 | 75% |
| 4/14/2019 | 2 | 2 | 1 | 1 | 5 | 4 | 80% |
On 4/5, 1 line was entered with data, so the completion is (1/1) = 100%.
No new data on 4/6 and no lines completed, so it remains (1/1) = 100%
One new line on 4/7 (but data not entered until 4/8), and no lines completed, so it reduces to (1/2) = 50%
On 4/8, two new lines entered with no data, but data entered on a previous open line, so it is (2/4) = 50%
On 4/9, no lines entered, data entered for one open line, and one line with data completes, so it is (2/3) = 67%
etc.
I am really struggling with the logic to build this concept in PowerBI. Any help/advice is greatly appreciated.
Thanks!
Hi jwesle
You can create columns to meet your demand.
Total Open Lines = CALCULATE(SUM(Table1[New Lines Entered]),FILTER(Table1,Table1[Calendar Date]<=EARLIER(Table1[Calendar Date])))-CALCULATE(SUM(Table1[Lines Completed]),FILTER(Table1,Table1[Calendar Date]<=EARLIER(Table1[Calendar Date])))
Total Open Lines with Data = CALCULATE(SUM(Table1[New Data Entered]),FILTER(Table1,Table1[Calendar Date]<=EARLIER(Table1[Calendar Date])))-CALCULATE(SUM(Table1[Lines with Data Completed]),FILTER(Table1,Table1[Calendar Date]<=EARLIER(Table1[Calendar Date])))
Data Percentage = DIVIDE(Table1[Total Open Lines with Data],Table1[Total Open Lines])
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUjge7rleNZCme1QkqZxfIYBpPSvFnHIRSiOrvkyukwbMQ?e=EdFK6i
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi jwesle
You can create columns to meet your demand.
Total Open Lines = CALCULATE(SUM(Table1[New Lines Entered]),FILTER(Table1,Table1[Calendar Date]<=EARLIER(Table1[Calendar Date])))-CALCULATE(SUM(Table1[Lines Completed]),FILTER(Table1,Table1[Calendar Date]<=EARLIER(Table1[Calendar Date])))
Total Open Lines with Data = CALCULATE(SUM(Table1[New Data Entered]),FILTER(Table1,Table1[Calendar Date]<=EARLIER(Table1[Calendar Date])))-CALCULATE(SUM(Table1[Lines with Data Completed]),FILTER(Table1,Table1[Calendar Date]<=EARLIER(Table1[Calendar Date])))
Data Percentage = DIVIDE(Table1[Total Open Lines with Data],Table1[Total Open Lines])
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EUjge7rleNZCme1QkqZxfIYBpPSvFnHIRSiOrvkyukwbMQ?e=EdFK6i
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.