Dynamic End of Contract by less than 60 days

[Anonymous]

Guys, i am trying to create a new EOC date column based on the Joining date.

if employee joined month is les than 60 days from now for example.

employee joined on 3/8/2021 and current date is 8/10/2021. joining month is 3 and which is less than 60 days from now the current month.

here we need to focus lessthan 60 days from current month not based on month minus 2.

in this case we need to extend the contract and EOC will be 3/7/2022. we are adding one more year with one day less.

if joining month is greater than 2 months or 60 days it should be current year.

i gave the examples in the spread sheet, i want the results as per highlighted in yellow.

i tried alot trust me i used many variables but of no use.

kindly help.

https://docs.google.com/spreadsheets/d/1zwsqkNrxZIoOh-fGFEgyKBUdPbJd5_JdIVmsO8p3W2Y/edit?usp=sharing

@amitchandak @Pragati11 @mwegener @BA_Pete @Anonymous @DebbieE @Jihwan_Kim 

[Anonymous · Accepted solution]

Hi @Anonymous 

Actually , I don’t quite understand your question .Please correct me if I wrongly understood your problem .

(1)The current date for you is 2021-08-10 ,and the date less than 60 days from the current date is 2021-06-11 .So if the DOJ new date is less than 2021-06-11 , it will return the date with one more year with one day less, for example , DOJ new date is 2021-05-02 ,then return 2022-05-01 ,right ?

(2)The current date is 2021-08-10 and DOJ new date is great than 60 days from the current date , will return current date .For example ,the DOJ new date is 2021-11-10 ,is great than days from the current date ,so will return 2021-11-10 ,right ?

If in this case ,you can create a column to judge .

EOC = IF('Table'[DOJ new]<DATE(2021,08,10)-60,'Table'[DOJ new]+364,IF('Table'[DOJ new]>DATE(2021,08,10)+60,'Table'[DOJ new]))

And the result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BA_Pete]

Hi @Anonymous ,

In Power Query, you can add a custom column something like this:

if [DOJ new] < Date.AddDays(Date.From(DateTime.LocalNow()), -60) then Date.AddDays(Date.AddYears([DOJ new], 1), -1) else null

Issues:

- I've made the conditional evaluation based on 60 days from today's date, but not sure if your question required this to be from the start of today's month or something.

- I've ended the conditional clause with 'else null' as I couldn't understand what you wanted to do if [DOJ new] was within 60 days of today.

- As your example data had no rows within 60 days of ACTUAL today (2021-08-10), there was no way to review the outcome of a negative condition.

I get the following output as-is:

It may be helpful if you can provide some more information around what you are trying to achieve, as I feel like I've misunderstood the requirements. Are you trying to flag when someone's annual contract is 60 days away from ending, so you want to calculate a new end date that it needs renewing until?

Pete

[Anonymous]

@BA_Pete i want to get the same output as i showed in the excel using DAX only.

kindly help

[BA_Pete]

Ok. It doesn't appear as though your outputs in your example follow any repeatable logic.

For example, you have a required output on the 29/09/2020 row, but no required output on either the 28/09/2020 or 30/09/2020 rows.

When talking about timeframes in terms of 'less than X days' and 'greater than X days' I would expect all dates either before or after 29/09/2020 to have required outputs, but this doesn't appear to be the case in the example you've provided.

Pete