Forum Discussion
Find the maximum date before the selected period
I have tables as below
- Sales table with employe code, date, and sales amount
- Training table with employee code and training date.
- DateDim table - relationship with date in sales and training table
I would like to summarise the sales per empolyee in the table by month or quarter or year. Also I would like to show the training date if employee attended training in a specific month. and if any training attendence before selected period, then it should show the last training attendence date prior to the selected period.
Sample TrainingTable
| Emp Code | Training Date |
| 101 | 01/01/2022 |
| 102 | 07/03/2022 |
| 103 | 11/03/2022 |
| 104 | 12/03/2022 |
| 105 | 02/10/2022 |
| 103 | 15/05/2022 |
| 106 | 03/01/2023 |
| 102 | 05/02/2023 |
| 103 | 15/08/2023 |
Expected result as below.
| EmpCode | Month | Sales Amount | Training Label |
| 103 | Jan-23 | 6283 | Previous Attendence on 15/5/2022 |
| 103 | Feb-23 | 7475 | |
| 103 | Mar-23 | 7529 | |
| 103 | Apr-23 | 3057 | |
| 103 | May-23 | 6461 | |
| 103 | Jun-23 | 6770 | |
| 103 | Jul-23 | 8888 | |
| 103 | Aug-23 | 9262 | 15/08/2023 |
| 103 | Sep-23 | 2210 | |
| 103 | Oct-23 | 6280 | |
| 103 | Nov-23 | 4312 | |
| 103 | Dec-23 | 5590 | |
| 102 | Jan-23 | 2294 | Previous Attendence on 7/3/2022 |
| 102 | Feb-23 | 4457 | |
| 102 | Mar-23 | 8389 | |
| 102 | Apr-23 | 5179 | |
| 102 | May-23 | 9464 | |
| 102 | Jun-23 | 4917 | |
| 102 | Jul-23 | 4421 | |
| 102 | Aug-23 | 6523 | |
| 102 | Sep-23 | 9047 | |
| 102 | Oct-23 | 2965 | |
| 102 | Nov-23 | 5351 | |
| 102 | Dec-23 | 7971 |
I can pull the training date for the respective month, but looking beyond the selected date range is challenging. below is the dax which I used but produces blank.
PreviousTraining =
CALCULATE(
MAX(TrainingTable[Training Date]),
FILTER(
ALL(TrainingTable),
TrainingTable[Training Date]<MIN(DateDim[Date]) && TrainingTable[Emp Code] = SELECTEDVALUE(Sales[EmpCode])
)
)
Please help me with the dax to look beyond the selected period and find the last training date per employee. Thanks in advance.
4 Replies
- ThxAlot
Super User
- jack555
Advocate III
ThxAlot thank a lot 😀. This is closer to what I look for. However training dates are repeated in each row. I would like to see the training date against the period only and if any previous attendence then first row should show the previous date; remaining of the rows where no attendence should be blank.
Below is the expected outcome.
mpCode Month Sales Amount Training Label 103 Jan-23 6283 Previous Attendence on 15/5/2022 103 Feb-23 7475 103 Mar-23 7529 103 Apr-23 3057 103 May-23 6461 103 Jun-23 6770 103 Jul-23 8888 103 Aug-23 9262 15/08/2023 103 Sep-23 2210 103 Oct-23 6280 103 Nov-23 4312 103 Dec-23 5590
- jack555
Advocate III
Please someone assist
- jack555
Advocate III
Any help?