cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors