DAX: percentage of actions closed on time (before due date) or late (after due date), Filtering out

[Anonymous]

Hello,

After a little help please. I'm setting up some visuals on data taken from an action tracker; example of some of the data I have in table below. I want to be able to provide some metrics on percentage of actions closed on time (before due date) or late (after due date).

UniqueID	ActionStatus	Duedate	Closed
Example1	Open	30 June 2021
Example2	Closed	30 July 2021	12 July 2021
Example3	Proposed Closed	12 July 2021
Example4	Closed	30 July 2021	01 July 2021
Example5	Closed	02 July 2021	05 July 2021

What I would like to do, is visualse in cards the percentage of those which were closed on time (closed date occurs before due date and Status is closed), and late (closed date after due date and Status is closed). Excluding those which are proposed closed and open.

I've tried a few measures but I'm lost!

[v-jingzhang]

Hi @Anonymous 

@AlexisOlson 's measures are good. Have you tried them? 

If you want percentages of the grand total, you could use

OnTime% = 
VAR OnTimeCount =
    CALCULATE (
        COUNT ( Table1[UniqueID] ),
        FILTER (
            Table1,
            Table1[ActionStatus] = "Closed"
                && Table1[Closed] <= Table1[Duedate]
        )
    )
VAR TotalCount = COUNT ( Table1[UniqueID] )
RETURN
    DIVIDE ( OnTimeCount, TotalCount )

Late% = 
VAR LateCount =
    CALCULATE (
        COUNT ( Table1[UniqueID] ),
        FILTER (
            Table1,
            Table1[ActionStatus] = "Closed"
                && Table1[Closed] > Table1[Duedate]
        )
    )
VAR TotalCount = COUNT ( Table1[UniqueID] )
RETURN
    DIVIDE ( LateCount, TotalCount )

Best Regards,
Community Support Team _ Jing

[AlexisOlson · Accepted solution]

If you define Late% like this:

Late% =
VAR LateCount =
    CALCULATE (
        COUNT ( Table1[UniqueID] ),
        FILTER (
            Table1,
            Table1[ActionStatus] = "Closed"
                && Table1[Closed] > Table1[Duedate]
        )
    )
VAR ClosedCount =
    CALCULATE (
        COUNT ( Table1[UniqueID] ),
        FILTER ( Table1, Table1[ActionStatus] = "Closed" )
    )
RETURN
    DIVIDE ( LateCount, ClosedCount )

Then OnTime% is simply

OnTime% = 1 - [Late%]