Need to calculate the date difference of date

[Anonymous]

Hi Team,

i have a requirement where i want to calculate the number of days between two days but not same as task planned days . Means task planned date will be start date when calculating the next no of days.

Incident Number	Task Code	Task Planned Start Date	Task Planned Finish Date	Days Between
10809139	9	8/15/2016	8/22/2017	372
10809139	15	10/1/2016	8/24/2017	327
10809139	18	5/18/2017	9/25/2017	130

Output required.

Incident Number	Task Code	Task Planned Start Date	Task Planned Finish Date	Days Between
10809139	9	8/15/2016	8/22/2017	372
10809139	15	8/22/2017	8/24/2017	2
10809139	18	8/24/2017	9/25/2017	32

final output: incident number = 372+2+32=406

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

Do you want to create the column Day between with the values of 372,2 and 32 or do you want a simple card visual with a single value of 406?

[Anonymous]

Hi Ashish,

I need to create a column with Day and show in the table.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous · Accepted solution]

Hi Ashish ,

I am not able to open the file as i don;t have current version in my system. Can you put the formula used to built this.

Thanks,

Rajveer

[Ashish_Mathur]

Hi,

First, this is the M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMLA0NLZU0lECYUNTXQMLXSMDQzMgx8gIyjFXitVBUWpoCiQMDHUNDeBqTXCqtQATugamEGmgWqAlllC1sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Incident Number" = _t, #"Task Code" = _t, #"Task Planned Start Date" = _t, #"Task Planned Finish Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Incident Number", Int64.Type}, {"Task Code", Int64.Type}, {"Task Planned Start Date", type date}, {"Task Planned Finish Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Incident Number", Order.Ascending}, {"Task Code", Order.Ascending}})
in
    #"Sorted Rows"

Then, this is the calculated column formula to calculated Days between

Days between = [Task Planned Finish Date]-IF(ISBLANK(CALCULATE(MAX(Table1[Task Planned Finish Date]),FILTER(Table1,Table1[Incident Number]=EARLIER(Table1[Incident Number])&&Table1[Task Code]<EARLIER(Table1[Task Code])))),[Task Planned Start Date],CALCULATE(MAX(Table1[Task Planned Finish Date]),FILTER(Table1,Table1[Incident Number]=EARLIER(Table1[Incident Number])&&Table1[Task Code]<EARLIER(Table1[Task Code]))))

Hope this helps.