Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |