DAX Expression to Count how many sales people have met or exceeded their sales quota for 2023

[DeanUW]

Hello,

I have a fact table that contains all of the sales transactions for all of the sales people (star schema with sales in one table, sales agents in another, and office data in a third merged into one central fact table).  I need to count how many sales people have a YTD sales total that exceeds there 2023 sales threshold.  The columns in the fact table include:

[Person ID] = unique key,  will appear multiple times in the table for each sale.

[Gross Amount] = calculated column that is simply [credits] - [debits] for every sales line. it is not calculating YTD total only the gross for each sale.

[2023 Threshold] = different values are given to different sales people.

[postingDate] = the date the sales transaction occured.

The co-pilot feature has not been much help creating a DAX expression that will count the distinct [Person ID] whose YTD total >= [2023 Threshold]; & my DAX skills are not quiet advanced enough.  Every attempt I have made only ends up counting the people who had a single transaction above their quota not their YTD total sales amount.

Thank you in advance to anyone who is able to assist!

[Take3 · Accepted solution]

@DeanUW 

Please try the following.

NOTE: I created the below test Fact table.  As you can see from the data, Ed & Joe met their threshold, while Sarah did not.  For this reason, 2 is the correct number of persons who exceeded their threshold for 2023.

Regards,

Nathan

P.S.  I used DAX to create my own Calendar table for Time Intelligence functions to work.