cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rajveer1117 Regular Visitor
Regular Visitor

Need to calculate the date difference of date

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 NumberTask CodeTask Planned Start DateTask Planned Finish DateDays Between
1080913998/15/20168/22/2017372
108091391510/1/20168/24/2017327
10809139185/18/20179/25/2017130

 

Output required.

 

Incident NumberTask CodeTask Planned Start DateTask Planned Finish DateDays Between
1080913998/15/20168/22/2017372
10809139158/22/20178/24/20172
10809139188/24/20179/25/201732

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Need to calculate the date difference of date

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

rajveer1117 Regular Visitor
Regular Visitor

Re: Need to calculate the date difference of date

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

5 REPLIES 5
Super User
Super User

Re: Need to calculate the date difference of date

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?

rajveer1117 Regular Visitor
Regular Visitor

Re: Need to calculate the date difference of date

Hi Ashish,

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

Super User
Super User

Re: Need to calculate the date difference of date

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

rajveer1117 Regular Visitor
Regular Visitor

Re: Need to calculate the date difference of date

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

Super User
Super User

Re: Need to calculate the date difference of date

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.