Power BI Direct Query DateDiff

[ss8551]

Hoping someone can assist me.  I have a PowerBI that I have linked with another PowerBI as a Direct Query.  I'm looking to find a way to do a DateDiff Formula to determine if a rep has been trained.  The Course, Completion Dates and Rep_ID's are in the Direct Query PBI.  The Desktop version has the Rep_ID, Date, and I'm looking to determine if the rep was trained.  The rep is considered trained the day after they completed the course.  There are multiple courses in the Driect Query Course Column and I'm looking only for specific Courses.  The Direct Query Table does not have all dates showing only the completion date.  The Desktop has all dates.   If there's a way to pull the course completion date into the Desktop version i may be able to use DateDiff but I've not been successful in determining how to accomplish this.  I may need to also have conditions if 2 courses are completed before they are considered to be trained.

 I tried to ask this in a different thread previously but was not able to get to a conclusion, so I've tried to be more specific on what I'm asking.  If there's a way to pull this data into the Desktop version from the Direct Query I may be able to use Date Diff but cannot find a way to accomplish this.  Please note there are many reps and this is a full calendar year (2022).

Below is a sample of some generic data to try to make it simplistic:

Direct Query
rep_id	comp_train	Course
a123		776655
a123	3/15/2022	887744
b456		776655
b456	4/16/2022	887744
b456		991133

Destop PBI
Date	rep_id	DateDiff comp_train > Date = Trained
3/14/2022	a123	Not_Trained
3/14/2022	b456	Not_Trained
3/15/2022	a123	Not_Trained
3/15/2022	b456	Not_Trained
3/16/2022	a123	Trained
3/16/2022	b456	Not_Trained
3/17/2022	a123	Trained
3/17/2022	b456	Not_Trained
4/15/2022	a123	Trained
4/15/2022	b456	Not_Trained
4/16/2022	a123	Trained
4/16/2022	b456	Not_Trained
4/17/2022	a123	Trained
4/17/2022	b456	Trained
4/18/2022	a123	Trained
4/18/2022	b456	Trained

[ss8551]

Community Support Team _ Eason,  many thanks.  I believe I'm able to use this.  I accepted this as a solution since it look like it's getting me what I need.  I really appreciate your help.

[ss8551]

This looks close, however, there are other courses with other completion dates.  When this occurs, it pulls the latest completion date of all of the courses (not for the specific course in question).  All courses are in one column, all rep ids are in one column, all completion dates are in one column.

It is therefore pulling the max date from the completion date column.  Is there a way to pull the Max Date for a rep_id for a course so it doesn't pull the Max date from the column but only the max date for that specific course and date and rep?  I modified the file that you had attached to show you what happens if a course completion date of 11/30/22 is entered.  You’ll notice that the Result is showing not trained for all since it’s using the max date of 11/30 instead of the max date of when the specific course was completed.  Sorry I couldn't find a way to re-upload the file that you had shared.

[v-easonf-msft]

Hi, @ss8551 

Not sure what you mean by 'pull the Max Date for a rep_id for a course '.

Please check the modifed pbix if it helps.

Best Regards,
Community Support Team _ Eason

[v-easonf-msft · Accepted solution]

Hi, @ss8551 

Not fully sure what you want.

Please check if formula below could help:

Result = 
VAR max_date =
    CALCULATE ( MAX ( 'Direct Query'[comp_train] ), ALL ( 'Direct Query' ) )
RETURN
    IF ( MAX ( 'Desktop PBI'[Date] ) > max_date, "Trained", "Not_Traind" )

Best Regards,
Community Support Team _ Eason