cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Dynamic End of Contract by less than 60 days

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.

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Anonymous
Not applicable

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

kindly help

Super User

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

Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors