Get count as per status date column

[nailed]

Hi everyone,

I'm trying to achive Count as per status date column like (Submission Date, Rejected Date, Approved Date, and Live Date).

Employee Tracker.pbix 

The main goal is to see how much total approved each employee submits and the variance will be calculated based on the total approved - target per employee and also filtered by month.

As for the rules, every live date is definitely approved. And there are cases where there are rejected dates and approved dates in the same row, then it is counted as approved not in rejected. The result is a dashboard filtered by month, by providing the headcounts, status count (submission, rejected, approved, and live)

Can you explain logically and guide me to successfully display.

PBI attached

Thank you

[amitchandak · Accepted solution]

@nailed , Two if you dates approved and rejected has some space or zero size text. Clean those in power query to make sure date type is date.

Two ways to deal with these measures. Based on their on date

Approved = calculate(countrows(compile), userelationship('Date'[Date], compile[Approved Date]))
Rejected = calculate(countrows(filter(compile,isblank(compile[Approved Date]) )), userelationship('Date'[Date], compile[Rejected Date]))
Submission = calculate(countrows(compile), userelationship('Date'[Date], compile[Submission Date]))

// based on submission date , assuming that is default active join
Approved = calculate(countrows(filter(compile,[status]= "Approved"))
Rejected = calculate(countrows(filter(compile,[status]= "Rejected"))
Submission = calculate(countrows(filter(compile,[status]= "Submission"))

Not you can use common date (joined with target too) and employee to analyze target together

[nailed]

Thank you, you're life saver! I want to ask again to you, how to returns 0 instead (Blank) based on your measure?

Thank you