Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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
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?
Hi Ashish,
I need to create a column with Day and show in the table.
Hi,
You may download my PBI file from here.
Hope this helps.
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
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.