DAX columns for date when sales target was reached by staff and number of days worked reach target

[Gangsta]

I woulkd be so greatful, if one of the DAX experts can help with a DAX formula for 2 columns I want to create.

I have 2 tables.

table 1 is called 'Staff' with 4 staff (A to D) in colum Staff, there are columns for Items Sold, Days Worked, Days To Meet Target and Date Target Met.

The related table is called 'Rota' has  columns Staff, Items Sold and Date Worked.

I need a DAX to formula for 3 additional columns in Staff table:

• Days Worked, this will count number of days each staff has worked.
• Days To Meet Target, work out the number of days staff worked to meet the target of 6 sales, ignoring days after target reached.
• Date Target Met, this will find the date when staff reached their target of 6 sales, ignoring days after target was reached.

In the Staff Table below,

Staff 'C' shows them meeting target of 6 after working 1 day, they met thier target on 2nd Feb, they worked 2 days in total. 

Staff 'D' shows them meeting target of 6 after working 2 days, the 2nd day they worked was 4th April, they worked 3 days in total.

Staff

Staff	Items Sold	Days Worked	Days To Meet Target	Date Target Met
A	3	2
B	2	1
C	9	2	1	02-Feb-20
D	7	3	2	04-Apr-20

Rota

Staff	Items Sold	Date Worked
A	1	13-Jan-20
C	6	02-Feb-20
B	2	14-Feb-20
D	3	18-Feb-20
A	2	21-Mar-20
D	3	04-Apr-20
C	3	05-Jun-20
D	1	01-Jul-20

[Gangsta]

@Greg_Deckler Thank you so much, I done, am well pleased with your solution and voted for your sticker. A quick test of all three columns shows they work as required, although the 'Date Target Met' has the time which I won't need, and likely to get in the way of afew queries so would be good to have just the date, not sure if it is easy to add this in the formula or should I  do this in helper column that reformats it to just date.

Again am very greatful for you to helping me out on this, saved me alot of head scratching since admittingly I am very new to DAX.

[Greg_Deckler]

@Gangsta Thanks! Easiest way to remove the time is to just change the Format for the column to Short date.

[Greg_Deckler · Accepted solution]

@Gangsta PBIX is attached below signature. If this helps, please vote for my sticker! Banana Pickle Math - Microsoft Fabric Community

Days Worked = COUNTROWS( DISTINCT( SELECTCOLUMNS( RELATEDTABLE( Rota ), "__Date", [Date Worked] ) ) )


Days To Meet Target = 
    VAR __Target = 6
    VAR __Table = RELATEDTABLE( 'Rota' )
    VAR __Table1 =
        ADDCOLUMNS(
            RELATEDTABLE( 'Rota' ),
            "__Cumulative", 
                VAR __Date = [Date Worked]
                VAR __Table = FILTER( __Table, [Date Worked] <= __Date )
                VAR __Result = SUMX( __Table, [Items Sold] )
            RETURN
                __Result
        )
    VAR __Result = IF( MAXX( __Table1, [__Cumulative]) < __Target, BLANK(), COUNTROWS( FILTER( __Table1, [__Cumulative] < __Target ) ) + 1)
RETURN
    __Result



Date Target Met = 
    VAR __Target = 6
    VAR __Table = RELATEDTABLE( 'Rota' )
    VAR __Table1 =
        ADDCOLUMNS(
            RELATEDTABLE( 'Rota' ),
            "__Cumulative", 
                VAR __Date = [Date Worked]
                VAR __Table = FILTER( __Table, [Date Worked] <= __Date )
                VAR __Result = SUMX( __Table, [Items Sold] )
            RETURN
                __Result
        )
    VAR __Result = MINX( FILTER( __Table1, [__Cumulative] >= __Target ), [Date Worked] )
RETURN
    __Result