Forum Discussion

Gaurav_Lakhotia's avatar
Gaurav_Lakhotia
Helper III
5 years ago
Solved

Calculated Column for Latest Training Date - 2

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 ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    5 years ago

    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's avatar
Gaurav_Lakhotia
Helper III
5 years ago

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's avatar
v-lili6-msft
Community Support
5 years ago

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's avatar
    Gaurav_Lakhotia
    Helper III
    5 years ago

    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.