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 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.

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

4 Replies

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