How to calculate the date diff to calculate the date diff based on a value of one column

[ss8551]

I have a table that I need to deteremine if a rep took a course on a date (post-trained) otherwise (pre-trained)
so that I can compare performance.

When I do this in Power BI I can find the date, however the trained date only appears in one column and therefore I cannot find the differnce between the date and course completed date since other dates are blank.
When this occurs it only shows as Post training on the date that the training occurred and all others are Pre-training. I need to have PBI calculate the differnce between the date that the training of a particular course(s)
based on the course number and the actual date. So in the example above the trained date - date = 0 and should be pre-trained. But the following day, it should show a date difference of 1 and be Post-Trained.

a sample data is below.
if it matters most of the data that I'm sourcing from is based on a direct query
When I try to do a calculation to deteremine Pre or Post it only looks at the trained date column to calculate the difference and returns one row.

Timeframe = IF('Training'[Course]=580683944,DATEDIFF(FIRSTDATE('Date'[Date]),MAX('Training'[Trained Date]),DAY))
Timeframe_2 = if('Training Class'[Timeframe]<1,"Pre-training","Post-Training")

In the example below I would want to show Post-Training after 3/15/22 ane Pre-training on 3/15/22 and prior.

Any Help is appreciated.

Date	employee_ID	Timeframe_date_diff	Pre-Post_Trained	Trained Date	Course	Trained Date
2/10/2022	zy645		Pre-training		356971895	2/10/2022
2/24/2022	zy645		Pre-training		585938423	2/24/2022
3/15/2022	zy645		Post-Training	3/15/2022	580683944	3/15/2022
3/16/2022	zy645		Pre-training		796620849	3/16/2022
4/4/2022	zy645		Pre-training		911100102	4/4/2022
4/20/2022	zy645		Pre-training		945554722	4/20/2022
5/4/2022	zy645		Pre-training		546021125	5/4/2022
6/30/2022	zy645		Pre-training		258477507	6/30/2022
7/13/2022	zy645		Pre-training		932521398	7/13/2022
7/19/2022	zy645		Pre-training		992119293	7/19/2022
7/26/2022	zy645		Pre-training		671259455	7/26/2022
7/27/2022	zy645		Pre-training		200181887	7/27/2022
8/2/2022	zy645		Pre-training		753239292	8/2/2022
9/14/2022	zy645		Pre-training		324184900	9/14/2022
10/6/2022	zy645		Pre-training		665890960	10/6/2022
10/12/2022	zy645		Pre-training		477715445	10/12/2022
10/13/2022	zy645		Pre-training		969323417	10/13/2022
10/20/2022	zy645		Pre-training		943344477	10/20/2022
10/26/2022	zy645		Pre-training		890212776	10/26/2022

[ss8551]

Thanks, of course you're correect.  however, i've had dfifficulties calculating the datediff between the two columns since the trained date only populates on one row.  is there a way to calculate the date diff between the trained date and date (sort of locking in on the one cell for the value of the trained date) or populate all the empty cells for dates before and after the trained date with the same value of trained date?  then i would be able to calculate the datediff between the two columns.  otherwise any tips that you can give me on index would be appreciated.  the row is coming from a direct query (liked from another powerbi source) and not sure about inexing or if this can be done with a direct query at all.

thanks again for any assistance.

[lbendlin]

the row is coming from a direct query

Next time please mention this fact earlier.

you cannot implement column logic in Direct Query connections if the data does not come from the same row.

[ss8551]

so you do not have any suggestions on a resolution to my isssue?  I did mention in my very first post that this was a direct query.  I stated this since I knew that this could be an issue.  Is there any way to either pull this data into antother tab and/or copy the data so that it fills all rows of the column?

[lbendlin]

You are right, I should have read your first post more attentively.  I do not think there is a  way to accomplish this in Power BI. This needs to be done at the data source.

[ss8551]

so i am using a direct query but, i also have other tabs.   Is it possible to pull this data into another tab of Power BI that is not a Direct Query and then accomplish the same?  I'm trying to use the data from the Direct Query to add to some additional data in Power BI that I have created using a Snowflake Connecton.  If I add a coulumn into one of my tabs can I pull this data into one of these tabs and fill the empty cells either in this column or another column with this same date (so for example date of 1/1/22 to today to be filled with the date the training completed date)?  so that I'm not trying to add it to a Direct Query directly but from a column i pulled in from a Direct Query  to a new column in My Power BI.

Not sure if this works but looking for any way to get this to function.

Thanks againd for your assistance.

[ss8551]

in the fields above i would like to calculate the datediff between the trained date and the date and have a value filled into the column of Timeframe_date_diff.  ultiimately i need to use this field to determine if <1 they are pretrained and >1 trained.  When i've attempted to do this it only populates one value and one result only for the date that they were trained.  it will show post trained on that date and all others appear as pretrained before or after that dated (likely due to the trained date column being empty except for the date that they were trained on).  Not sure of how to resolve this and/or if it may be needed/possible to fill the empty cells above and/or below the trained date with the value of the date that they were trained on.

Thank you for any assistance you can provide.

[lbendlin]

Power BI has no concept of "above" or "below".  It is your responsibility to indicate how rows should be addressed/sorted (for example by adding an index column).

[lbendlin]

Please indicate what you mean by "difference" - where would that show as a result based on your sample data?