Forum Discussion
Solved
Calculated column based on two other column
Hello altruists,
Need some help here.
I have 3 columns of data with millions of rows ( File, record and Time). Each file may have multiple records. Would like to create 4 new columns calculating time difference between two records ( 0 to 1, 1 to 2 and so on ) for each file. Expected outputs are showed in last 4 columns ( Record_0to1 and so on) . Would appreciate any help with this. Thanks
| File | Record | Time | Record_0to1 | Record_1to2 | Record_2to3 | Record_3to4 |
| A | 0 | 100 | 10 | 15 | 5 | 40 |
| A | 1 | 90 | 10 | 15 | 5 | 40 |
| A | 2 | 75 | 10 | 15 | 5 | 40 |
| A | 3 | 70 | 10 | 15 | 5 | 40 |
| A | 4 | 30 | 10 | 15 | 5 | 40 |
| B | 0 | 200 | 20 | 30 | 10 | 20 |
| B | 1 | 180 | 20 | 30 | 10 | 20 |
| B | 2 | 150 | 20 | 30 | 10 | 20 |
| B | 3 | 140 | 20 | 30 | 10 | 20 |
| B | 4 | 120 | 20 | 30 | 10 | 20 |
| C | 0 | 400 | 50 | 40 | 60 | 50 |
| C | 1 | 350 | 50 | 40 | 60 | 50 |
| C | 2 | 310 | 50 | 40 | 60 | 50 |
| C | 3 | 250 | 50 | 40 | 60 | 50 |
| C | 4 | 200 | 50 | 40 | 60 | 50 |
3 Replies
Hi,
Thanks for your reply. What are you referring to as sum here >>
[Sum]
This is what I ahve at the end
Diff 0 to 1 = VAR __filter = ALLEXCEPT ( 'Table', 'Table'[File] ) VAR __valStart = CALCULATE ( SUM(TABLE.Time), __filter, 'Table'[Record] = 0 ) VAR __valEnd = CALCULATE ( [SUM(TABLE.Time),__filter, 'Table'[Record] = 1 ) RETURN __valStart - __valStart
