cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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
1 ACCEPTED SOLUTION
Super User

@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 =
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 =
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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Frequent Visitor

@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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

@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 =
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 =
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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors