Forum Discussion

jack555's avatar
jack555
Icon for Advocate III rankAdvocate III
2 years ago

Find the maximum date before the selected period

I have tables as below

  1. Sales table with employe code, date, and sales amount
  2. Training table with employee code and training date.
  3. 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 CodeTraining Date
10101/01/2022
10207/03/2022
10311/03/2022
10412/03/2022
10502/10/2022
10315/05/2022
10603/01/2023
10205/02/2023
10315/08/2023

 

Expected result as below. 

EmpCodeMonthSales AmountTraining Label
103Jan-236283Previous Attendence on 15/5/2022
103Feb-237475 
103Mar-237529 
103Apr-233057 
103May-236461 
103Jun-236770 
103Jul-238888 
103Aug-23926215/08/2023
103Sep-232210 
103Oct-236280 
103Nov-234312 
103Dec-235590 
102Jan-232294Previous Attendence on 7/3/2022
102Feb-234457 
102Mar-238389 
102Apr-235179 
102May-239464 
102Jun-234917 
102Jul-234421 
102Aug-236523 
102Sep-239047 
102Oct-232965 
102Nov-235351 
102Dec-237971 

 

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

    • jack555's avatar
      jack555
      Icon for Advocate III rankAdvocate 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. 

       

      mpCodeMonthSales AmountTraining Label
      103Jan-236283Previous Attendence on 15/5/2022
      103Feb-237475 
      103Mar-237529 
      103Apr-233057 
      103May-236461 
      103Jun-236770 
      103Jul-238888 
      103Aug-23926215/08/2023
      103Sep-232210 
      103Oct-236280 
      103Nov-234312 
      103Dec-235590