How do I count certain records in one table based while excluding records based on date values in another table?

Question

I am tasked with Producing count of Users asked to Acknowledge various Documents. However the data is an event log and the data model does not match the business rules.

I am not very familiar with PowerBI or DAX and this problem is taxing my brain.

The system I am trying to report on is a document management system. A Document goes through two workflows.

1. The Authoring Workflow where an Editor firsts Edits and then Publishes a Document.
2. The Acknowledgement Workflow where Users are asked to Acknowledge the Published Document. This step can be repeated several times concurrently or sequentially.

• Optionally repeat #1 and #2 several times per Document.

The business rules dictate once a document is published the previous Acknowledgement Requests are to be ignored, however the Acknowledgement Requests in the Tables.

For example if a document has been through the Authoring and Acknowledgement cycle twice, the fist time 5 users were asked to acknowledge. The second time 2 users were asked to acknowledge. There is another document where 1 user were asked to Acknowledge, the total number of Users Requested is 3. (2+1) Not 8. (5+2+1)

All data is present in the report, the relationships are defined. The business logic may not be changed, and the data model may have columns and tables added to it. So long as they are derived from the existing data.

Thank you so much in advance!

// Tables
Document: Id, Description
Activity: Workflow Id*, Type, User, Executed, Acknowledged
Workflow: Document Id*, Workflow Id, Type, 

Sample Data

Document

ID	Description
D1	Document One
D2	Document Two

Workflow

Document Id	Type	Workflow Id
D1	Authoring	W1
D1	Acknowledgement	W2
D1	Acknowledgement	W3
D1	Authoring	W4
D1	Acknowledgement	W5
D2	Authoring	W6
D2	Acknowledgement	W7

Activity

Workflow Id	Type	User	Executed	Acknowledgement
W1	Edited	U1	1/1/2000	na (null)
W1	Published	U1	1/3/2000	na
W2	Request Acknowledgement	U2	1/4/2000	null
W2	Request Acknowledgement	U3	1/4/2000	1/5/2000
W3	Request Acknowledgement	U4	1/5/2000	null
W3	Request Acknowledgement	U5	1/5/2000	1/5/2000
W3	Request Acknowledgement	U6	1/5/2000	1/5/2000
W4	Edited	U1	1/6/2000	na (null)
W4	Published	U1	1/7/2000	na (null)
W5	Request Acknowledgement	U2	1/8/2000	null
W5	Request Acknowledgement	U6	1/8/2000	1/9/2000
W6	Edited	U1	1/1/2000	na (null)
W6	Published	U1	1/3/2000	na (null)
W7	Request Acknowledgement	U2	1/5/2000	1/8/2000

Workflow W4 should cause the previous 5 requests from W2, and W3 to be ignored.

Expected Results: A bar chart containing two bars.

Total Requests     | === 3
Completed Requests | == 2

Answer 1

Basically you need to identify the most recent 'Publish' activity for each document and most recent 'Acknowledgment' Request.

For Publish You can do this by adding a calculated column to the Workflow table. something like this should work

CALCULATE(
    MAX(Activity[Executed]),
    FILTER(
        Activity,
        Activity[Workflow Id] = EARLIER(Workflow[Workflow Id])
        && Activity[Type] = "Published"
    )
)

For 'Acknowledgment' create another calculated column

VAR CurrentWorkflowId = Activity[Workflow Id]
VAR DocumentId = LOOKUPVALUE(Workflow[Document Id], Workflow[Workflow Id], CurrentWorkflowId)
VAR MostRecentPublishDate = CALCULATE(
                                MAX(Workflow[MostRecentPublishDate]),
                                FILTER(
                                    Workflow,
                                    Workflow[Document Id] = DocumentId
                                )
                            )
RETURN IF(Activity[Type] = "Request Acknowledgement" && Activity[Executed] > MostRecentPublishDate, TRUE, FALSE)

Then you just need to create two measures to count them Total requests

CALCULATE(
    COUNTROWS(Activity),
    Activity[IsRelevantAcknowledgment] = TRUE
)

and Total Acknowledgements

CALCULATE(
    COUNTROWS(Activity),
    Activity[IsRelevantAcknowledgment] = TRUE,
    NOT ISBLANK(Activity[Acknowledged])
)

let me know if this works