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.
Hello all,
I need jedis' mind.
Consider I have following data:
Task_ID Date_Created Date_Completed
1 08/08/18 12:05:02 08/08/18 12:06:08
2 07/08/18 12:25:02 78/08/18 12:26:08
Simplier
Task_ID Date_Created Date_Completed
1 x x+1
2 y y+1
3 z z+1
4 f f +1
Etc
What I need to do is to calculate difference between Date_Completed and Date_Created, but not for row 1, row 2, etc - I would use DATEDIF function, but rather like this: Date_Created (task_id = 2) MINUS Date_Completed (task_id=1) ====> y-(x+1)
I try to use EARLIER function to return me Date_Completed in a newly calculated column (not measure, I checked), and then calculate the difference normally with DATEDIF.
BUT I constantly get an error that either it "refers to an earlier row context which doesn't exist", or something similar.
I need to do it in such a way that than it will calculate this time difference for the whole column in the same way:
Date_Created (task_id = 2) MINUS Date_Completed (task_id=1)
Date_Created (task_id = 3) MINUS Date_Completed (task_id=2)
Date_Created (task_id = 4) MINUS Date_Completed (task_id=3)
==>
y-(x+1)
z-(y+1)
f-(z+1)
So the cell is not a fixed one but rather a dynamic one (that's why LOOKUPVALUE didn't work for me). I always refer to a date associated with a previous task_id (tas_id-1).
I kindly ask you for your help because I really cannot find any solution. Thank you in advance.
Solved! Go to Solution.
Hey,
here you will find a little tutorial on how to create Calculated Columns in Power BI
https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns
This describes how to use variable in DAX
https://powerbi.tips/2017/05/using-variables-within-dax/
This describes the DAX function DATEDIFF
https://msdn.microsoft.com/en-us/query-bi/dax/datediff-function-dax
Regards,
Tom
Hey,
I created two calculated columns, the 1st returns the "Date_Completed" value from the previous Task_ID (currentID - 1) and the 2nd calculates the difference in seconds.
Date_Created_prev = var currentTaskID = 'Table1'[Task_ID] return CALCULATE( MAX('Table1'[Date_Completed]) ,FILTER( ALL(Table1) ,'Table1'[Task_ID] = currentTaskID - 1 ) )
DateDiff = DATEDIFF('Table1'[Date_Created],'Table1'[Date_Created_prev],SECOND)
Based on your sample data (I tweeked it a little, because I thought there were typos) the result looks like this:
Hopefully this is what you are looking for.
Regards,
Tom
Hello,
Wow, that was fast. Sorry for a dummy question, but where to I place this in? Is is a query or what?
What does this mean?
var currentTaskID
And this SECOND?
DateDiff = DATEDIFF('Table1'[Date_Created],'Table1'[Date_Created_prev],SECOND)
Thanks!
Hey,
here you will find a little tutorial on how to create Calculated Columns in Power BI
https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns
This describes how to use variable in DAX
https://powerbi.tips/2017/05/using-variables-within-dax/
This describes the DAX function DATEDIFF
https://msdn.microsoft.com/en-us/query-bi/dax/datediff-function-dax
Regards,
Tom
Hello everyone here again,
I just got additional conditions to the problem stated above. So now I have the following data :
Parent_task_ID Task_ID Date_Created Date_Completed
888 1 08/08/18 12:05:02 08/08/18 12:06:08
888 2 07/08/18 12:25:02 08/08/18 12:26:08
999 3 08/08/18 13:05:02 08/08/18 13:06:08
999 4 07/08/18 13:25:02 08/08/18 13:26:08
What I need to do is to calculate difference between Date_Completed and Date_Created, but not for row 1, row 2, etc - I would use DATEDIF function, but rather like this: Date_Created (task_id = 2) MINUS Date_Completed (task_id=1)
Now I need to do this procedure for each Parent_task_ID
Could please anybody here help me with this? Thank you so much!
Thank you, I will investigate that!
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |