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,
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
Solved! Go to Solution.
Hi @Anonymous ,
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
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.
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.
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
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
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.
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
Hi Marcus,
The total in the result section is not giving the right number. The total should be 26 rather than 7.
Thanks,
Raj
Hi @Anonymous ,
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Thank you vey much for your support.
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.
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |