Calculated Column for Latest Training Date

[Gaurav_Lakhotia]

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 date after 28 days of the Training Date. (For Example: If Training Date is 1st Sept 2020 than Latest Date will be 29th Sept 2020 i.e 1 Sept 2020 + 28 Days )

In case a second training was conducted within 28 days period of training for same Customer, the period end date will be a 28 days after second training. For Example: First Training Date is 1st Sept 2020 and within 28 days of First Training we've another Training on 15th Sept 2020, than the latest date will be 13 Oct 2020 i.e. 15th Sept + 28 Days, for both training.

Here is the file with sample data,

 Sample File 

Please help me out.

Thanks.

[amitchandak · Accepted solution]

@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)

[Greg_Deckler]

@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]

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-2020	Jessica	Building a Strong Front Desk Team	Sweet House Day Spa - Houston
04-09-2020	Brittany	Building a Strong Front Desk Team	101 Skin Lab
06-09-2020	Brittany	Inventory Control / Product Management	A New Leaf Flowers, Gifts, & More
06-05-2020	Brittany	Advanced Modality Treatments	Sweet House Day Spa
18-06-2020	Jennifer	Advanced Modality Treatments	Aidan James Salon
26-06-2020	Wendy	Advanced Modality Treatments	101 Market
16-06-2020	Jessica	Building a Strong Front Desk Team	Sweet House Day Spa
07-09-2020	Becky	Building a Strong Front Desk Team	502 Flair
23-05-2020	Brittany	Advanced Modality Treatments	Sweet House Day Spa

[amitchandak · Accepted solution]

@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 · Accepted solution]

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]

@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

[Gaurav_Lakhotia]

Got the solution.
Thank you @amitchandak