Oh wow. That was prompt.
Thank you all for the input.
@Rajesh Thanks for the sample code. I will play with that now and come back with an update if it works for what I am trying to show. I wanted to get some input to @BrianJ and @AntrikshSharma.
@BrianJ and @AntrikshSharma the thinking/goal behind the goals/logic is quite simple. At least in my head it is
One goal/target might be to nominate one person to give a presentation on a topic by a certain date. Another goal might be to have arranged a client feedback meeting by a particular date.
Let’s say Unit 1 nominates two people to give the presentation before the due target date. That is counted as two separate counts and thus having exceeded the target. To simplify things, although it is two separate counts, I take the latest date in that month that the goal was achieved as the date the count was achieved.
Both Units have the same target due by date. Both could meet the target by that date by carrying out the activity just once, or multiple times, in which case it is counted as “multiple” entries. The ultimate goal being to try and encourage these practices/behaviour within the business units.
I am not sure which is considered one or many in those cases. It seems it can change.
My main target, with the DAX request is to try and highlight where a business unit has missed a target. Even if they are overachieving some targets they may be neglecting others that are due by a particular window. And to try and show which ones have been missed for a particular month in an easy to see way.
Following on from looking at Rajesh’s proposed solution I must add that it would be ideal if when a goal has been met (in an earlier month before it is due) then it is accounted for in the month when due. I had not thought of that, but that is important and perhaps complicates the requirement a bit.
I hope that clarifies things for you both. But if not, kindly let me know.
I appreciate your time spent on this and would be interested in the approaches that you would both would use to show this as I can learn/apply this in other scenarios I have in mind for using these simple targets.
@BrianJ. Please find the Excel file attached if it helps.
Also, I put this anonymous sample data together before coming across your really handy Enterprise DNA video on Anonymising Data Will use your approach as I get more problems I need advice on in here. But Power BI is something I currently only dabble in part time at work. Trying to show/demo more use cases so I can get more full time work on it.
Test Data.xlsx (19.6 KB)