topic Calculating Expected Due/Completion Date Time based on SLA & Created Date Time in DAX Commands and Tips

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

Anonymous — Fri, 07 Feb 2020 06:53:44 GMT

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

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

az38 — Fri, 07 Feb 2020 07:37:20 GMT

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

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

Anonymous — Fri, 07 Feb 2020 07:45:36 GMT

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)

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

Anonymous — Fri, 07 Feb 2020 07:53:00 GMT

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.

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

az38 — Fri, 07 Feb 2020 07:55:31 GMT

Anonymous

try something like

+SELECTEDVALUE(YourTable[SLA Days])

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

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

Anonymous — Fri, 07 Feb 2020 08:27:47 GMT

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.

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

Anonymous — Fri, 07 Feb 2020 09:35:16 GMT

Hi az38 , Unfortunately SELECTEDVALUE() didnt work !

Any other way you suggest ?

Thanks

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

az38 — Fri, 07 Feb 2020 09:38:02 GMT

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

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

Anonymous — Fri, 07 Feb 2020 09:50:08 GMT

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.

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

Anonymous — Fri, 07 Feb 2020 11:09:30 GMT

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.

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

Anonymous — Fri, 07 Feb 2020 11:22:07 GMT

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

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

Anonymous — Fri, 07 Feb 2020 12:32:33 GMT

Thanks Anonymous It worked perfect !