cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## 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 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,

Thanks.

2 ACCEPTED SOLUTIONS
Super User

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

Helper III

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

6 REPLIES 6
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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
Super User

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

Helper III

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

Super User

@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

Helper III

Got the solution.
Thank you @amitchandak

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors