Forum Discussion
Helper IIHow to calculate the date diff to calculate the date diff based on a value of one column
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 |
8 Replies
- lbendlin
Super User
Please indicate what you mean by "difference" - where would that show as a result based on your sample data?
ss8551in 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).
- 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 queryNext 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?
