cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gaurav_Lakhotia
Helper III
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,

 Sample File 

 

Please help me out.

Thanks.

2 ACCEPTED SOLUTIONS

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

View solution in original post

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

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
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>)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

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

@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

 

Got the solution.
Thank you @amitchandak 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors