Forum Discussion

Gaurav_Lakhotia's avatar
Gaurav_Lakhotia
Helper III
5 years ago
Solved

Calculated Column for Latest Training Date

Hi All,   I need help in calculating the calculated column for latest date.   Scenario is, There is a TrainingTracker table which has Date column (i.e. Training Date). I need to find out the da...
calculated column
Dax Help
Need Help DAX
  • amitchandak's avatar
    amitchandak
    5 years ago

    Gaurav_Lakhotia , From what I got.

    You can have new column which tell diff from last training and you can use that

    new column =
    Datediff( maxx(filter(table ,[Customers Name] = earlier([Customers Name]) && [TrainingDate] <earlier([TrainingDate])),[TrainingDate]),[TrainingDate],day)

  • Gaurav_Lakhotia's avatar
    Gaurav_Lakhotia
    5 years ago

    Hi amitchandak ,

     

    The DAX you've shared is calculating by taking very first Customer Training date as a base.

     

    We want it to consider every corresponding training date of each row to search within 28 days after training.

     

    Thanks

Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

Gaurav_Lakhotia I will look at PBIX but could you just use:

 

New Column = [Training Date] + 28

New Column 1 = IF([Second Training Date] < [New Column],[Second Training Date]+28,<something else>)

 

Gaurav_Lakhotia's avatar
Gaurav_Lakhotia
Helper III
5 years ago

Hi Greg_Deckler ,

 

We've Customer Name column. We've to lookup training dates customer wise. Here is sample data, look for first 2 entries of Sweet House Day Spa. We've 6 May 2020 and 16 May 2020.

 

TrainingDate   Trainer'sName           Trainer'sType                                                         Customer's Name

02-09-2020Jessica   Building a Strong Front Desk TeamSweet House Day Spa - Houston
04-09-2020BrittanyBuilding a Strong Front Desk Team101 Skin Lab 
06-09-2020BrittanyInventory Control / Product ManagementA New Leaf Flowers, Gifts, & More
06-05-2020BrittanyAdvanced Modality TreatmentsSweet House Day Spa
18-06-2020JenniferAdvanced Modality TreatmentsAidan James Salon
26-06-2020WendyAdvanced Modality Treatments101 Market
16-06-2020JessicaBuilding a Strong Front Desk TeamSweet House Day Spa
07-09-2020BeckyBuilding a Strong Front Desk Team502 Flair
23-05-2020BrittanyAdvanced Modality TreatmentsSweet House Day Spa
  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Gaurav_Lakhotia , From what I got.

    You can have new column which tell diff from last training and you can use that

    new column =
    Datediff( maxx(filter(table ,[Customers Name] = earlier([Customers Name]) && [TrainingDate] <earlier([TrainingDate])),[TrainingDate]),[TrainingDate],day)

    • Gaurav_Lakhotia's avatar
      Gaurav_Lakhotia
      Helper III
      5 years ago

      Hi amitchandak ,

       

      The DAX you've shared is calculating by taking very first Customer Training date as a base.

       

      We want it to consider every corresponding training date of each row to search within 28 days after training.

       

      Thanks

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        Gaurav_Lakhotia , Please find the file attached after the signature. I created 2 columns to see if those can help.

        Column names are different as they did not get pasted correctly