cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ss8551
Helper II
Helper II

Power BI Direct Query DateDiff

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_idcomp_trainCourse
a123 776655
a1233/15/2022887744
b456 776655
b4564/16/2022887744
b456 991133

 

Destop PBI
Daterep_idDateDiff comp_train > Date = Trained
3/14/2022a123Not_Trained
3/14/2022b456Not_Trained
3/15/2022a123Not_Trained
3/15/2022b456Not_Trained
3/16/2022a123Trained
3/16/2022b456Not_Trained
3/17/2022a123Trained
3/17/2022b456Not_Trained
4/15/2022a123Trained
4/15/2022b456Not_Trained
4/16/2022a123Trained
4/16/2022b456Not_Trained
4/17/2022a123Trained
4/17/2022b456Trained
4/18/2022a123Trained
4/18/2022b456Trained
1 ACCEPTED SOLUTION
v-easonf-msft
Community Support
Community Support

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

View solution in original post

4 REPLIES 4
ss8551
Helper II
Helper II

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
Helper II
Helper II

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.

 

Direct Query 2.png

Direct Query 1.png

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
Community Support
Community Support

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.