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.
I am trying to make Power BI search for any identical rows, except for the "date/time" values, and report the time between them in a new column. So i want new column that calculate the time between two rows (by the difference in a "Time/Date" column), provided that several other values are identical (e.g. same costumer-ID, same product, same store) IF such rows exists.
In other words, if the costumer-ID, product-ID and Store-ID are the same as in an earlier row, calculate the time (e.g. days, months or years) that has past since the earlier row. In the example table below, i would like to have the calculated column return a value only in row 4 (the time difference between row 1 and row 4).
Index | Time/date | Costumer_ID | product_ID |
1 | 25.03.2005 | 1 | D |
2 | 26.03.2005 | 2 | B |
3 | 29.03.2005 | 1 | A |
4 | 04.04.2005 | 1 | D |
Any ideas?
Solved! Go to Solution.
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.... You have essentially the same scenario.
Hi @Awikant
You can add a column like below.
column =
VAR __thisRowDate = 'Table'[Time/date]
VAR __previousDate =
CALCULATE(
MAX( 'Table'[Time/date] ),
ALLEXCEPT( 'Table', 'Table'[Costumer_ID], 'Table'[product_ID] ),
'Table'[Time/date] < __thisRowDate
)
RETURN IF( __previousDate > 0, __thisRowDate - __previousDate )
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.... You have essentially the same scenario.
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |