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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors