Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Chris2016
Resolver I
Resolver I

Count after comparing dates from distinct tables

Hello,

I need help with the following scenario.

I have a table where  sales representatives' training information exists:

Sales RepTrainingDate
AX12/1/2024
AY15/2/2024
AZ17/3/2024
BX 
BY12/2/2024
BZ 
CX 
CY2/2/2024
DX3/3/2024
DY2/3/2024
DZ 

 

 

And there is also a table that shows the transactions done by each sales rep:

Sales RepTransaction IDTransaction Date
A12/1/2024
A212/1/2024
A316/2/2024
B417/3/2024
B51/2/2024
B62/3/2024
C72/4/2024
C83/1/2024
D911/1/2024
D109/1/2024
D113/4/2024
E1213/3/2024
E1316/3/2024
E145/3/2024



I need to evaluate sales reps' performance after the trainings  by calculating the count of Transaction ids considering that Transaction Date should be after or equal to the Training Date.

The output should be something like:

Sales RepTrainingTransaction count after trainingTransaction Date
AX112/1/2024
AX116/2/2024
DX13/4/2024
AY116/2/2024
BY117/3/2024
CY12/4/2024
DY13/4/2024
AZ0 
BY12/3/2024


Which I can then use to assess the training metadata like this:

TrainingTransaction monthTransaction count after training
XJan-241
XFeb-241
XApr-241
YFeb-241
YMar-242
YApr-242
Z 0


Any idea how this can be achieved? I need to do this in DAX as the tables are very large and cannot do more transformations in PQ.
Thanks a lot for any help.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

see attached.  You still need to invest in a proper data model.

 

lbendlin_0-1713642410000.png

 

View solution in original post

2 REPLIES 2
Chris2016
Resolver I
Resolver I

Thanks a lot, this worked!

 

Best regards!

lbendlin
Super User
Super User

see attached.  You still need to invest in a proper data model.

 

lbendlin_0-1713642410000.png

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.