Date Difference between task

[Anonymous]

Hi Team,

Need help in calculating the date differecne between task.

Number	Task	Comp Date	Result Expected	Logic
100241	10	21-Oct-19	0	
100241	20	29-Oct-19	8	Task 20-task 10
100241	30	1-Nov-19	2	Task 30-task 20
100241	40	1-Nov-19	1	task 40-Task 30
100242	10	15-Oct-19	0	
100242	20	20-Oct-19	5	Task 20-task 10
100242	30	25-Oct-19	5	Task 30-task 20
100242	40	30-Oct-19	5	task 40-Task 30

[Ashish_Mathur]

Hi,

Try this calculated column formula

=[Comp Date]-CALCULATE(MAX(Data[Comp Date]),FILTER(Data,Data[Number]=EARLIER(Data[Number])&&Data[Comp Date]<EARLIER(Data[Comp Date])))

Hope this helps.

[mwegener]

Hi @Anonymous 

check this out.

You may download my PBIX file from here.
Hope this helps.

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Anonymous]

Hi Marcus,

Thanks for the solution. The only thing which i forget to mention is task is the text field rather than number. So when doing with text it is not working.

Number	Task	Comp Date	Result Expected	Logic
100241	Task10	21-Oct-19	0	
100241	Task20	29-Oct-19	8	Task 20-task 10
100241	Task30	1-Nov-19	2	Task 30-task 20
100241	Task40	1-Nov-19	1	task 40-Task 30
100242	Task10	15-Oct-19	0	
100242	Task20	20-Oct-19	5	Task 20-task 10
100242	Task30	25-Oct-19	5	Task 30-task 20
100242	Task40	30-Oct-19	5	task 40-Task 30

[Jimmy801]

Hello @Anonymous 

I would suggest to do this in power Query. Here an example

let
	Source = #table
	(
		{"Number","Task","Comp Date"},
		{
			{"100241","10","43759"},	{"100241","20","43767"},	{"100241","30","43770"},	{"100241","40","43770"},	{"100242","10","43753"},	{"100242","20","43758"},	
			{"100242","30","43763"},	{"100242","40","43768"}
		}
	),
    ToDate = Table.TransformColumns(Source,{{"Comp Date", each Date.From(Number.From(_)), type date}}),
    Group = Table.Group(ToDate, {"Number"}, {{"AllRows", each _, type table [Number=text, Task=text, Comp Date=date]}}),
	AddIndex = Table.TransformColumns
	(
		Group,
		{{"AllRows", each Table.AddIndexColumn(_,"Index",1)}}
	),
	CalculationDuration= Table.TransformColumns
	(
		AddIndex,
		{{"AllRows", (tableint)=>
		Table.AddColumn(tableint, "Difference", (add)=>
		if add[Index]=1 then 0 else Duration.TotalDays(add[Comp Date]- tableint[Comp Date]{add[Index]-2}))}}
	),
    DeleteOtherColumns = Table.SelectColumns(CalculationDuration,{"AllRows"}),
    ExpandColumns = Table.ExpandTableColumn(DeleteOtherColumns, "AllRows", {"Number", "Task", "Comp Date", "Difference"}, {"Number", "Task", "Comp Date", "Difference"})
in
    ExpandColumns

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[mwegener]

Hi @Anonymous ,

try this.

Result =
VAR PREV =
    CALCULATE (
        MAX ( 'Table'[Comp Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            VALUE ( SUBSTITUTE ( 'Table'[Task], "Task", "" ) )
                = MAXX ( 'Table', VALUE ( SUBSTITUTE ( 'Table'[Task], "Task", "" ) ) ) - 10
                && 'Table'[Number] = MAX ( 'Table'[Number] )
        )
    )
VAR DateDiff =
    DATEDIFF ( PREV, MAX ( 'Table'[Comp Date] ), DAY )
RETURN
    IF ( ISBLANK ( DateDiff ), 0, DateDiff )

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[v-lili6-msft]

hi @Anonymous 

If so, just adjust it as below:

Step1:

Add a rank column for [Task] column of each [Number]

Task Rank = RANKX(FILTER('Table','Table'[Number]=EARLIER('Table'[Number])),'Table'[Task],,ASC)

Step2:

Then use [Task Rank] column instead of [Task] in the measure formula as below:

New Result = 
VAR PREV =
    CALCULATE (
        MAX ( 'Table'[Comp Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Task Rank]
                = MAX ( 'Table'[Task Rank] ) - 1
                && 'Table'[Number] = MAX ( 'Table'[Number] )
        )
    )
VAR DateDiff =
    DATEDIFF ( PREV, MAX ( 'Table'[Comp Date] ), DAY )
RETURN
    IF ( ISBLANK ( DateDiff ), 0, DateDiff )

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Hi Marcus,

The total in the result section is not giving the right number. The total should be 26 rather than 7.

Thanks,
Raj

[mwegener · Accepted solution]

Hi @Anonymous ,

If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Anonymous]

Thank you vey much for your support.

[mwegener]

Hi @Anonymous 

try this

Result =
VAR _tbl =
    SUMMARIZE (
        'Table',
        'Table'[Number],
        'Table'[Task],
        'Table'[Comp Date],
        "Prev", CALCULATE (
            MAX ( 'Table'[Comp Date] ),
            FILTER (
                ALL ( 'Table' ),
                VALUE ( SUBSTITUTE ( 'Table'[Task], "Task", "" ) )
                    = MAXX ( 'Table', VALUE ( SUBSTITUTE ( 'Table'[Task], "Task", "" ) ) ) - 10
                    && 'Table'[Number] = MAX ( 'Table'[Number] )
            )
        )
    )
RETURN
    SUMX (
        _tbl,
        IF ( ISBLANK ( [Prev] ), 0, DATEDIFF ( [Prev], [Comp Date], DAY ) )
    )

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.