Forum Discussion
Get count as per status date column
Hi everyone,
I'm trying to achive Count as per status date column like (Submission Date, Rejected Date, Approved Date, and Live Date).
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
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
2 Replies
- amitchandak
Super User
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
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
