Calculating Expected Due/Completion Date Time based on SLA & Created Date Time

[Anonymous]

Hi,

Looking for a help on DateTime Caculation.

I have a table with Unique Tasks Ids and its allocated dateTime and each of it has an SLA in days defined.

I need to create every Task's expected Completion date as per its SLA considering WorkingDays i.e. WeekDays.

The challenge is, eg. 2 Tasks can have same allocated Date Time with different SLA and the expected date time should have time precision. Eg. below;

Thanks !

Task ID	Allocate DateTime	SLA Days	Expected_Completion DateTime
2100	24/01/2020 15:06	1	27/01/2020 15:06
2101	24/01/2020 15:06	5	31/01/2020 15:06
2102	28/01/2020 12:05	2	30/01/2020 12:05

[Anonymous]

refer this one

https://community.powerbi.com/t5/Desktop/Add-working-days-to-a-date/td-p/146945

You need create date table and also you need to identify which one weekday and weekend in date table only and link date table on allocation date.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Try this one as well

New column=

var Quotient_=QUOTIENT(WEEKDAY(Test[Allocate DateTime],1)+Test[SLA Days],7)

return

Expected End Date = Test[Allocate DateTime]+Test[SLA Days]+2*Quotient_

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[az38]

Hi @Anonymous 

use technique from this thread

https://community.powerbi.com/t5/Desktop/Add-working-days-to-a-date/td-p/146945

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Many thanks for your response @Anonymous !

I have tried that and the only issue is adding the Days to the Rank.

In my case the value "10" is SLA and Dynamic coming from another Table, where the SLA for each datetime in the row.

When I try to add it to the Rank (as the last parameter), it asks for aggrregation, where the output fails, since though the datetime it referes could be same but the SLA are different - (my table example above) !

Appreciate your help on it ..

Add 10 woring days = LOOKUPVALUE(Canlendar[Date],Canlendar[Identify],1,Canlendar[Rank1],Canlendar[Rank1]+10)

[az38]

@Anonymous 

try something like

+SELECTEDVALUE(YourTable[SLA Days])

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38 , Unfortunately SELECTEDVALUE() didnt work !

Any other way you suggest ?

Thanks

[az38]

@Anonymous 

did you create relationships?

did you try 

Result=RELATED(Calendar[Add 10 woring days])

as mentioned in example?

Add 10 woring days is a column from Calendar table

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

@az38 

Yes, I have created relationship on the Dates between the Calendar & the Fact [1 : *] .

Also, did all the steps including fetching the related column as a Result  in Fact table.

The issue is Dynamic SLA number (i.e. days) which in the Fact table occuring multiple times for a single date (as in my example).
Need a way to Add SLA as is,  at the step ' Add 10 working day step' without Aggregartion (i.e. Min/ Max)

Thanks.

[Anonymous · Accepted solution]

QUOTIENT = QUOTIENT(WEEKDAY(Test[Allocate DateTime],1)+Test[SLA Days],7)

Expected End Date = Test[Allocate DateTime]+Test[SLA Days]+2*Test[QUOTIENT]

Thanks,

Pravin

[Anonymous]

Thanks @Anonymous   It worked perfect !

[Anonymous]

hi @Anonymous 

Did you tried my solution?

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.