Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
Can someone help me create a calculated column to get the ouput below?
ID | Date | Index | Output |
A | 8/3/2023 7:45:05 | 1 | |
A | 8/3/2023 7:45:15 | 2 | 0:00:10 |
A | 8/3/2023 7:45:17 | 3 | 0:00:02 |
B | 8/3/2023 7:45:21 | 1 | |
B | 8/3/2023 7:45:27 | 2 | 0:00:06 |
A | 8/3/2023 7:45:27 | 4 | 0:00:10 |
A | 8/3/2023 7:46:01 | 5 | 0:00:34 |
A | 8/3/2023 7:46:15 | 6 | 0:00:14 |
C | 8/3/2023 7:46:31 | 1 | |
C | 8/3/2023 7:46:50 | 2 | 0:00:19 |
C | 8/3/2023 7:47:20 | 3 | 0:00:30 |
B | 8/3/2023 7:47:40 | 3 | 0:02:13 |
Solved! Go to Solution.
Hi @Leslie1015 ,
Calculated column:
Output 2 =
VAR CurrentID = TableName[ID]
VAR CurrentIndex = TableName[Index]
VAR CurrentDate = TableName[Date]
VAR PreviousRow =
CALCULATE (
MAX ( TableName[Date] ),
ALLEXCEPT ( TableName, TableName[ID] ),
TableName[Index] = CurrentIndex - 1
)
RETURN
IF (
CurrentIndex > 1,
CurrentDate - PreviousRow,
BLANK ()
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi @Leslie1015 ,
Calculated column:
Output 2 =
VAR CurrentID = TableName[ID]
VAR CurrentIndex = TableName[Index]
VAR CurrentDate = TableName[Date]
VAR PreviousRow =
CALCULATE (
MAX ( TableName[Date] ),
ALLEXCEPT ( TableName, TableName[ID] ),
TableName[Index] = CurrentIndex - 1
)
RETURN
IF (
CurrentIndex > 1,
CurrentDate - PreviousRow,
BLANK ()
)
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
@Leslie1015 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous