Percentual overview depending on partials of days

[Pavlous]

Hey,

I've ran into a problem when i was triing to make an overview of percentual completion of tasks per each day. I have matrix which should show me how many of tasks where completed "on time" (dependant on due date).  I want only to compare the completed ones (on the date when they were completed) and shows how many of them where completed on time and how many were not completed on time. e.g. 86.7% On time. I am also interested only on category "A". And by default due to other columns is the table set to be connected wíth ref_date by end time. I've tried to use USERELEATIONSHIP to connect due date to date but it still not showing the correct numbers, as far as I know that some days it should be 100% and its not showing them. I dont know what I am missing...

Tried by this formula, but I propably need to make another table...

Note.: This formula reffers to Archive table (where data is stored after archivation) and live table. Also the span is set due to some tolerances, but they are not so important. The result(total) is table of all unprocessed tasks from received date until end date. (Also tried with the original ones - End Time and Due Date)

Processed on time1 =
CALCULATE (
    IF (
        SUM ( 'CAS Tasks'[Cycle time] ) <= 3,
        SUM ( 'CAS Tasks'[Processed_] )
            / CALCULATE (
                SUM ( 'CAS Tasks'[To process] ),
                FILTER ( 'CAS Tasks', SUM ( 'CAS Tasks'[Cycle time] ) >= 3 )
            ),1-
        SUM ( 'CAS Tasks'[Processed_] )
            / CALCULATE (
                SUM ( 'CAS Tasks'[To process] ),
                FILTER ( 'CAS Tasks', SUM ( 'CAS Tasks'[Cycle time] ) >= 3 )
            )
    ),
    FILTER ( 'CAS Tasks', 'CAS Tasks'[Category] = "Agreements" ),
    USERELATIONSHIP ( 'CAS Tasks'[Due Date], Ref_date[Date] )
)
    + CALCULATE (
        IF (
            SUM ( Archive[Cycle time] ) <= 3,
            (
                    SUM ( Archive[Processed_] )
                        / CALCULATE (
                            SUM ( Result[Total] ),
                            FILTER ( Archive, SUM ( Archive[Cycle time] ) >= 3 )
                        )
                ),
   1-
                     (
                    SUM ( Archive[Processed_] )
                        / CALCULATE (
                            SUM ( Result[Total] ),
                            FILTER ( Archive, SUM ( Archive[Cycle time] ) >= 3 )
                        )
                )
        ),
        FILTER ( Archive, Archive[Categories] = "Agreements" ),
        USERELATIONSHIP ( Archive[Due Date], Ref_date[Date] )
    )

Received Date	Due Date	End Time	Categories	To process
09/08/2017	10/08/2017	11/08/2017	C	3
09/08/2017	10/08/2017	11/08/2017	C	1
09/08/2017	10/08/2017	11/08/2017	C	1
09/08/2017	10/08/2017	11/08/2017	C	1
09/08/2017	10/08/2017	11/08/2017	C	1
09/08/2017	10/08/2017	11/08/2017	C	1
09/08/2017	10/08/2017	13/08/2017	C	1
09/08/2017	10/08/2017	13/08/2017	C	1
09/08/2017	10/08/2017	13/08/2017	C	1
09/08/2017	10/08/2017	09/08/2017	A	21
09/08/2017	10/08/2017	11/08/2017	A	25
09/08/2017	10/08/2017	11/08/2017	A	25
09/08/2017	10/08/2017	11/08/2017	A	25
09/08/2017	10/08/2017	11/08/2017	A	25
09/08/2017	10/08/2017	11/08/2017	A	25
09/08/2017	10/08/2017	11/08/2017	A	11
09/08/2017	10/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	10/08/2017	A	25
09/08/2017	10/08/2017	11/08/2017	A	25
09/08/2017	10/08/2017	11/08/2017	A	25
09/08/2017	10/08/2017	11/08/2017	A	25
09/08/2017	13/08/2017	11/08/2017	A	25
09/08/2017	14/08/2017	12/08/2017	A	25
09/08/2017	15/08/2017	12/08/2017	A	25
09/08/2017	16/08/2017	12/08/2017	A	25
09/08/2017	17/08/2017	12/08/2017	A	25
09/08/2017	18/08/2017	12/08/2017	A	3
09/08/2017	19/08/2017	12/08/2017	A	25
09/08/2017	20/08/2017	12/08/2017	A	25
09/08/2017	21/08/2017	12/08/2017	A	25
09/08/2017	13/08/2017	12/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	12
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	2
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	A	25
09/08/2017	14/08/2017	14/08/2017	B	4

[v-ljerr-msft]

Hi @Pavlous,

What is [Cycle time] column? I can see it in your formula, but not in the shared sample data. 

And what's your expected result against the shared sample data? Could you be more precisely with it?

Regards

[Pavlous]

Hi @v-ljerr-msft,

Cycle time column is only datediff between due date and end time. As I mentioned its not critical for this issue.

I would need to show percentual overwiev of tasks done in time per day. If i have done 100 tasks with due date on today and another 20 with same due date i have not done it should be 83.3% tasks done in time. In this simple case its easy.

Imagine another situation. I receive 200 tasks on 1/1/2017 with due date on 21/1/2017. I will process 20 on 1/1/2017 another 20 3/1/2017 and 100 on 21/1/2017. I would like to show that i have completed those 140 tasks from 200 only by the due date. So at line with date 21/1/2017 I would have 70% completed in time.

Are those informations sufficient?

[Pavlous]

Corrected table

Received Date	Due Date	End Time	Categories	To process	Processed
09/08/2017	10/08/2017	11/08/2017	C	3	3
09/08/2017	10/08/2017	11/08/2017	C	1	1
09/08/2017	10/08/2017	11/08/2017	C	1	1
09/08/2017	10/08/2017	11/08/2017	C	1	1
09/08/2017	10/08/2017	11/08/2017	C	1	1
09/08/2017	10/08/2017	11/08/2017	C	1	1
09/08/2017	10/08/2017	13/08/2017	C	1	1
09/08/2017	10/08/2017	13/08/2017	C	1	1
09/08/2017	10/08/2017	13/08/2017	C	1	1
09/08/2017	10/08/2017	09/08/2017	A	21	21
09/08/2017	10/08/2017	11/08/2017	A	25	25
09/08/2017	10/08/2017	11/08/2017	A	25	25
09/08/2017	10/08/2017	11/08/2017	A	25	25
09/08/2017	10/08/2017	11/08/2017	A	25	25
09/08/2017	10/08/2017	11/08/2017	A	25	25
09/08/2017	10/08/2017	11/08/2017	A	11	11
09/08/2017	10/08/2017	10/08/2017	A	25	25
09/08/2017	10/08/2017	10/08/2017	A	25	25
09/08/2017	10/08/2017	10/08/2017	A	25	25
09/08/2017	10/08/2017	10/08/2017	A	25	25
09/08/2017	10/08/2017	11/08/2017	A	25	25
09/08/2017	10/08/2017	11/08/2017	A	25	25
09/08/2017	10/08/2017	11/08/2017	A	25	25
09/08/2017	13/08/2017	11/08/2017	A	25	25
09/08/2017	14/08/2017	12/08/2017	A	25	25
09/08/2017	15/08/2017	12/08/2017	A	25	25
09/08/2017	16/08/2017	12/08/2017	A	25	25
09/08/2017	17/08/2017	12/08/2017	A	25	25
09/08/2017	18/08/2017	12/08/2017	A	3	3
09/08/2017	19/08/2017	12/08/2017	A	25	25
09/08/2017	20/08/2017	12/08/2017	A	25	25
09/08/2017	21/08/2017	12/08/2017	A	25	25
09/08/2017	13/08/2017	12/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	12	12
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	2	2
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	A	25	25
09/08/2017	14/08/2017	14/08/2017	B	4	4