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