Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
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 |
Solved! Go to 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
@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.
@Gangsta Thanks! Easiest way to remove the time is to just change the Format for the column to Short date.
@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
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
29 | |
28 | |
12 | |
12 | |
12 |