Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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%]
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
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%]
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
87 | |
67 | |
49 |
User | Count |
---|---|
135 | |
113 | |
100 | |
68 | |
67 |