Calculated Column for Latest Training Date - 2

[Gaurav_Lakhotia]

Hi All, 

Scenario:
1. 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 ).

2. In case more training sessions were conducted within 28 days period of training for same Customer, the period end date will be  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.

3. In case, there is a gap of 28 days between Training then we should consider the next training as the base date, for all the further calculation.

Here is the file with sample data, Sample File 

Please help me out.

 @amitchandak , @Greg_Deckler  , You guys help me out with the similar problem, we did not consider Scenario 3. 

Thanks.

[v-lili6-msft · Accepted solution]

hi @Gaurav_Lakhotia 

For your case, add two calculate column as below:

durationbetweennextdate = DATEDIFF('Training Tracker'[Date],MINX (
        FILTER (
            'Training Tracker',
            'Training Tracker'[Customer Name]
                = EARLIER ( 'Training Tracker'[Customer Name] )
                && 'Training Tracker'[Date] > EARLIER ( 'Training Tracker'[Date] )
        ),
        'Training Tracker'[Date]
    ),DAY

)

Result = MAXX(TOPN(1,FILTER('Training Tracker', 'Training Tracker'[Customer Name]
                = EARLIER ( 'Training Tracker'[Customer Name] )&& 'Training Tracker'[Date] >= EARLIER ( 'Training Tracker'[Date] )&&([durationbetweennextdate]>=28||[durationbetweennextdate]=BLANK())
                ),[Date],ASC),[Date])+28

Result:

regards,

Lin

[Gaurav_Lakhotia]

Hi @v-lili6-msft ,

Thanks for the solution. It looks great.

I just had 1 more query, is there a way to identify the first date of each cycle in a column ?

Please refer image, "Column: Valid Training", 

[v-lili6-msft]

HI  @Gaurav_Lakhotia 

Just add another column as below:

Valid Training = IF( 'Training Tracker'[Date]= MINX(FILTER('Training Tracker',
            'Training Tracker'[Customer Name]
                = EARLIER ( 'Training Tracker'[Customer Name] )
                &&'Training Tracker'[Result]=EARLIER('Training Tracker'[Result])), 'Training Tracker'[Date]),"Y","N")

Result: 

Regards,

Lin

[Gaurav_Lakhotia]

Hi @v-lili6-msft ,

This is fine but it won't handle duplicate records, could you please help me with that?

For ex, If we have two trainings on same day with same client. We only require one training as valid training. Trainer Type can be different,

We can also have an Index column, if that helps.