Calculating Completion Percent Based on Multiple Date Fields

[jwesle]

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!

[v-xicai · Accepted solution]

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/EUjge7rleNZCme1Qkq...

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.