cancel
Showing results 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

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

 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!

1 ACCEPTED SOLUTION
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%]``
2 REPLIES 2
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 )``````

Best Regards,
Community Support Team _ Jing

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

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors