Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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

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).

 

UniqueIDActionStatusDuedateClosed
Example1Open30 June 2021 
Example2Closed30 July 202112 July 2021
Example3Proposed Closed12 July 2021 
Example4Closed30 July 202101 July 2021
Example5Closed 02 July 202105 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!

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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%]

View solution in original post

2 REPLIES 2
v-jingzhang
Community Support
Community Support

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 )

21112501.jpg

 

Best Regards,
Community Support Team _ Jing

AlexisOlson
Super User
Super User

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%]

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.