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

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

Gangsta — Tue, 06 Aug 2024 17:32:46 GMT

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

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

Greg_Deckler — Tue, 06 Aug 2024 18:36:20 GMT

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

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

Gangsta — Tue, 06 Aug 2024 22:52:18 GMT

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.

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

Greg_Deckler — Wed, 07 Aug 2024 08:35:51 GMT

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