Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I am new to Bi and having a hard time in calculating the difference between consecutive rows for a particular ID.
I want to caculate the difference between consecutive volume for a particular "Unique ID".
As the ID changes, the cumulative difference calculation should correspond to that particular "Unique ID"
Thnaks in advance.
Solved! Go to Solution.
Hi @hmeegada,
1. Add an index in the Query Editor.
2. Add a calculated column with the formula below.
Column = VAR currentIndex = [Index] VAR currentVolume = [Volume.Volume] VAR lastVolume = CALCULATE ( MIN ( Table1[Volume.Volume] ), FILTER ( ALLEXCEPT ( 'Table1', Table1[Unique ID] ), Table1[Index] = currentIndex - 1 ) ) RETURN IF ( ISBLANK ( lastVolume ), 0, currentVolume - lastVolume )
Best Regards,
Dale
Hi @hmeegada,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @hmeegada,
What's the expected result? Since the "Unique" is unique, you can just calculate the number of "Unique".
[Unique] count('table'[Unique])
Best Regards,
Dale
Proud to be a PBI Community Champion
Hi, I have provided a sample data below for your reference and the last column is the expected result
The difference between consecutive rows should continue for a particular Unique ID and as soon as the Unique ID changes, the difference between consecutive rows should correspond to that particular Unique ID.
Site # | Product | Tank # | Volume.Inventory Date | Volume.Volume | Unique ID | Expected Result |
0711150 | DIESEL | 3 | 10/18/2018 4:54:00 AM | 11797 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 5:26:00 AM | 11797 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 5:41:00 AM | 11797 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 5:56:00 AM | 11797 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 6:11:00 AM | 11797 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 6:26:00 AM | 11797 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 6:41:00 AM | 11797 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 6:56:00 AM | 11782 | 07111503 | -15 |
0711150 | DIESEL | 3 | 10/18/2018 7:11:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 7:26:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 7:41:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 7:56:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 8:11:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 8:26:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 8:41:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 8:56:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 9:11:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 9:26:00 AM | 11782 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 9:41:00 AM | 11759 | 07111503 | -23 |
0711150 | DIESEL | 3 | 10/18/2018 9:56:00 AM | 11691 | 07111503 | -68 |
0711150 | DIESEL | 3 | 10/18/2018 10:11:00 AM | 11691 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 10:26:00 AM | 11691 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 10:41:00 AM | 11654 | 07111503 | -37 |
0711150 | DIESEL | 3 | 10/18/2018 10:57:00 AM | 11654 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 11:11:00 AM | 11654 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 11:26:00 AM | 11654 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 11:41:00 AM | 11654 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 11:56:00 AM | 11654 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 12:11:00 PM | 11654 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 12:26:00 PM | 11654 | 07111503 | 0 |
0711150 | DIESEL | 3 | 10/18/2018 12:41:00 PM | 11631 | 07111503 | -23 |
0711150 | DIESEL | 3 | 10/18/2018 12:56:00 PM | 11631 | 07111503 | 0 |
0711150 | REG | 1 | 10/18/2018 4:54:00 AM | 33130 | 07111501 | 0 |
0711150 | REG | 1 | 10/18/2018 5:26:00 AM | 33080 | 07111501 | -50 |
0711150 | REG | 1 | 10/18/2018 5:41:00 AM | 33046 | 07111501 | -34 |
0711150 | REG | 1 | 10/18/2018 5:56:00 AM | 33016 | 07111501 | -30 |
0711150 | REG | 1 | 10/18/2018 6:11:00 AM | 33016 | 07111501 | 0 |
0711150 | REG | 1 | 10/18/2018 6:26:00 AM | 32997 | 07111501 | -19 |
0711150 | REG | 1 | 10/18/2018 6:41:00 AM | 32955 | 07111501 | -42 |
0711150 | REG | 1 | 10/18/2018 6:56:00 AM | 32929 | 07111501 | -26 |
0711150 | REG | 1 | 10/18/2018 7:11:00 AM | 32865 | 07111501 | -64 |
0711150 | REG | 1 | 10/18/2018 7:26:00 AM | 32850 | 07111501 | -15 |
0711150 | REG | 1 | 10/18/2018 7:41:00 AM | 32762 | 07111501 | -88 |
0711150 | REG | 1 | 10/18/2018 7:56:00 AM | 32744 | 07111501 | -18 |
0711150 | REG | 1 | 10/18/2018 8:11:00 AM | 32539 | 07111501 | -205 |
0711150 | REG | 1 | 10/18/2018 8:26:00 AM | 32229 | 07111501 | -310 |
0711150 | REG | 1 | 10/18/2018 8:41:00 AM | 32161 | 07111501 | -68 |
0711150 | REG | 1 | 10/18/2018 8:56:00 AM | 31983 | 07111501 | -178 |
0711150 | REG | 1 | 10/18/2018 9:11:00 AM | 31881 | 07111501 | -102 |
0711150 | REG | 1 | 10/18/2018 9:26:00 AM | 31604 | 07111501 | -277 |
0711150 | REG | 1 | 10/18/2018 9:41:00 AM | 31570 | 07111501 | -34 |
0711150 | REG | 1 | 10/18/2018 9:56:00 AM | 31506 | 07111501 | -64 |
0711150 | REG | 1 | 10/18/2018 10:11:00 AM | 31441 | 07111501 | -65 |
0711150 | REG | 1 | 10/18/2018 10:26:00 AM | 31400 | 07111501 | -41 |
0711150 | REG | 1 | 10/18/2018 10:41:00 AM | 31256 | 07111501 | -144 |
0711150 | REG | 1 | 10/18/2018 10:57:00 AM | 31195 | 07111501 | -61 |
0711150 | REG | 1 | 10/18/2018 11:11:00 AM | 31161 | 07111501 | -34 |
0711150 | REG | 1 | 10/18/2018 11:26:00 AM | 31074 | 07111501 | -87 |
0711150 | REG | 1 | 10/18/2018 11:41:00 AM | 30878 | 07111501 | -196 |
0711150 | REG | 1 | 10/18/2018 11:56:00 AM | 30840 | 07111501 | -38 |
0711150 | REG | 1 | 10/18/2018 12:11:00 PM | 30681 | 07111501 | -159 |
0711150 | REG | 1 | 10/18/2018 12:26:00 PM | 30635 | 07111501 | -46 |
0711150 | REG | 1 | 10/18/2018 12:41:00 PM | 30529 | 07111501 | -106 |
0711150 | REG | 1 | 10/18/2018 12:56:00 PM | 30401 | 07111501 | -128 |
0711150 | PREM | 2 | 10/18/2018 4:54:00 AM | 13970 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 5:26:00 AM | 13970 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 5:41:00 AM | 13970 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 5:56:00 AM | 13955 | 07111502 | -15 |
0711150 | PREM | 2 | 10/18/2018 6:11:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 6:26:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 6:41:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 6:56:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 7:11:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 7:26:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 7:41:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 7:56:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 8:11:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 8:26:00 AM | 13955 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 8:41:00 AM | 13932 | 07111502 | -23 |
0711150 | PREM | 2 | 10/18/2018 8:56:00 AM | 13872 | 07111502 | -60 |
0711150 | PREM | 2 | 10/18/2018 9:11:00 AM | 13872 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 9:26:00 AM | 13872 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 9:41:00 AM | 13872 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 9:56:00 AM | 13830 | 07111502 | -42 |
0711150 | PREM | 2 | 10/18/2018 10:11:00 AM | 13834 | 07111502 | 4 |
0711150 | PREM | 2 | 10/18/2018 10:26:00 AM | 13834 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 10:41:00 AM | 13834 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 10:57:00 AM | 13819 | 07111502 | -15 |
0711150 | PREM | 2 | 10/18/2018 11:11:00 AM | 13807 | 07111502 | -12 |
0711150 | PREM | 2 | 10/18/2018 11:26:00 AM | 13807 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 11:41:00 AM | 13750 | 07111502 | -57 |
0711150 | PREM | 2 | 10/18/2018 11:56:00 AM | 13750 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 12:11:00 PM | 13709 | 07111502 | -41 |
0711150 | PREM | 2 | 10/18/2018 12:26:00 PM | 13709 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 12:41:00 PM | 13709 | 07111502 | 0 |
0711150 | PREM | 2 | 10/18/2018 12:56:00 PM | 13648 | 07111502 | -61 |
0711157 | PREMIUM | 2 | 10/18/2018 5:02:00 AM | 38849 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 5:35:00 AM | 38849 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 5:50:00 AM | 38853 | 07111572 | 4 |
0711157 | PREMIUM | 2 | 10/18/2018 6:05:00 AM | 38853 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 6:20:00 AM | 38853 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 6:35:00 AM | 38853 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 6:50:00 AM | 38853 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 7:05:00 AM | 38853 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 7:20:00 AM | 38853 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 7:35:00 AM | 38853 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 7:50:00 AM | 38853 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 8:05:00 AM | 38853 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 8:20:00 AM | 38800 | 07111572 | -53 |
0711157 | PREMIUM | 2 | 10/18/2018 8:35:00 AM | 38788 | 07111572 | -12 |
0711157 | PREMIUM | 2 | 10/18/2018 8:50:00 AM | 38788 | 07111572 | 0 |
PREMIUM | 2 | 10/18/2018 9:05:00 AM | 38728 | 07111572 | -60 | |
0711157 | PREMIUM | 2 | 10/18/2018 9:20:00 AM | 38728 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 9:35:00 AM | 38724 | 07111572 | -4 |
0711157 | PREMIUM | 2 | 10/18/2018 9:50:00 AM | 38724 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 10:05:00 AM | 38724 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 10:20:00 AM | 38724 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 10:35:00 AM | 38705 | 07111572 | -19 |
0711157 | PREMIUM | 2 | 10/18/2018 10:50:00 AM | 38705 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 11:05:00 AM | 38705 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 11:20:00 AM | 38705 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 11:35:00 AM | 38705 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 11:50:00 AM | 38705 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 12:05:00 PM | 38705 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 12:20:00 PM | 38682 | 07111572 | -23 |
0711157 | PREMIUM | 2 | 10/18/2018 12:35:00 PM | 38682 | 07111572 | 0 |
0711157 | PREMIUM | 2 | 10/18/2018 12:50:00 PM | 38663 | 07111572 | -19 |
0711157 | PREMIUM | 2 | 10/18/2018 1:05:00 PM | 38652 | 07111572 | -11 |
0711157 | RUL | 1 | 10/18/2018 5:02:00 AM | 33739 | 07111571 | 0 |
0711157 | RUL | 1 | 10/18/2018 5:35:00 AM | 33735 | 07111571 | -4 |
0711157 | RUL | 1 | 10/18/2018 5:50:00 AM | 33682 | 07111571 | -53 |
Thanks
Hi @hmeegada,
1. Add an index in the Query Editor.
2. Add a calculated column with the formula below.
Column = VAR currentIndex = [Index] VAR currentVolume = [Volume.Volume] VAR lastVolume = CALCULATE ( MIN ( Table1[Volume.Volume] ), FILTER ( ALLEXCEPT ( 'Table1', Table1[Unique ID] ), Table1[Index] = currentIndex - 1 ) ) RETURN IF ( ISBLANK ( lastVolume ), 0, currentVolume - lastVolume )
Best Regards,
Dale
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |