topic Re: Adding the differences of cumulative data in a column in DAX Commands and Tips

Adding the differences of cumulative data in a column

upamanyu — Fri, 02 Dec 2022 22:04:36 GMT

Hello everyone,

I have a situation to calculate the sum of differences for a series of cumulative values. The data is basically cumulative product count from a machine that resets itself randomly after which it returns a "0" value (This is preventing me to use simple difference between MAX & MIN values). I need to create a DAX measure to get the sum of filled products (the column values) by calculating the differences of this cumulative data. Thank you so much for your time and support. Really greatful for your help.

Below is a sample data from the machine where it got reset twice returning a "0" value. The output for this data is supposed to be: 2624.

Filled Products
4632
4639
4640
4642
4643
4645
4646
4650
4651
4657
4658
4665
4666
4669
4670
4677
4678
4687
4688
4698
4701
4708
4714
4817
4818
4870
0
8
9
16
17
28
29
34
35
42
43
50
51
54
55
60
61
80
81
88
89
94
95
102
103
109
110
112
113
118
119
170
171
181
182
193
194
202
203
211
212
225
226
233
234
238
239
242
243
246
247
248
249
253
254
255
256
257
261
262
266
267
303
304
312
313
323
324
332
333
341
343
357
359
369
370
381
382
396
397
409
411
414
415
419
420
430
431
441
442
457
458
470
471
477
478
533
534
616
617
625
626
634
635
645
646
663
664
673
674
684
685
694
695
699
700
705
706
713
714
715
716
729
730
737
739
752
753
763
764
775
776
785
786
793
794
832
833
848
849
859
860
870
871
881
882
887
888
896
897
908
909
914
915
926
927
933
934
939
940
949
950
954
955
958
959
965
966
973
974
985
986
987
988
995
1001
1023
1024
1036
1037
1044
1045
1046
1047
1051
1052
1060
1063
1069
1070
1074
1075
1083
1085
1087
1088
1096
1098
1108
1109
1114
1115
1116
1117
1120
1121
1128
1134
1158
1159
1164
1165
1168
1169
1177
1178
1183
1184
1187
1188
1191
1192
1198
1199
1203
1204
1210
1211
1217
1218
1221
1222
1228
1229
1232
1233
1242
1243
1254
1255
1267
1268
1274
1280
1393
1394
1402
1403
1408
1409
1417
1418
1426
1427
1430
1431
1443
1444
1452
1453
1482
1483
1490
1491
1501
1502
1515
1516
1522
1523
1531
1532
1546
1548
1557
1558
1563
1564
1573
1574
1599
1600
1607
1608
1616
1617
1623
1624
1634
1635
1651
1652
1662
1663
1679
1680
1690
1691
1699
1701
1711
1712
1726
1727
1738
1739
1747
1748
1751
1752
1758
1759
1766
1767
1773
1774
1785
1786
1796
1797
1831
1832
1844
1845
1857
1858
1863
1864
1868
1869
1889
1890
1904
1905
1924
1925
1937
1938
1951
1953
1958
1959
1966
1967
1980
1982
1990
1991
1998
1999
2010
2011
2023
2024
2035
2036
2041
2042
2047
2048
2077
2078
2090
2091
2098
2099
2106
2107
2114
2115
2122
2123
2130
2131
2141
2142
2146
2147
2159
2160
2170
2171
2182
2183
2191
2192
2204
2206
2234
0
3
4
29
30
55
61
151
152

Re: Adding the differences of cumulative data in a column

Jihwan_Kim — Sat, 03 Dec 2022 08:23:02 GMT

Hi,

I assume the table has a kind of time sequence column or a time stamp column.

In my sample, I used index column.

Please check the below measure and the attached pbix file.

expected result measure: = VAR _conditiontable = ADDCOLUMNS ( ADDCOLUMNS ( Data, "@prevvalue", MAXX ( FILTER ( Data, Data[ID] = VAR _previd = MAXX ( FILTER ( Data, Data[ID] < EARLIEST ( Data[ID] ) ), Data[ID] ) RETURN _previd ), Data[Filled Products] ) ), "@condition", IF ( [@prevvalue] <= Data[Filled Products], 0, 1 ) ) VAR _grouptable = ADDCOLUMNS ( _conditiontable, "@group", SUMX ( FILTER ( _conditiontable, Data[ID] <= EARLIEST ( Data[ID] ) ), [@condition] ) ) VAR _groupall = GROUPBY ( _grouptable, [@group], "@min", MINX ( CURRENTGROUP (), Data[Filled Products] ), "@max", MAXX ( CURRENTGROUP (), Data[Filled Products] ) ) RETURN SUMX ( _groupall, [@max] - [@min] )

Re: Adding the differences of cumulative data in a column

upamanyu — Sun, 04 Dec 2022 03:53:30 GMT

Hello Kim,
Thank you so much for your time and help. Could you please help me in adding a modification to the code?

All the rows have a corresponding date with just the date as a column. For instance, the data posted here is for one date. How do i make this calculation repeat for that particular date only?

Thank you.

Re: Adding the differences of cumulative data in a column

upamanyu — Sun, 04 Dec 2022 04:19:34 GMT

Also, I have null values in between the values like below screen shot.

Can you please add the calculation per date and avoid these null values in the DAX code.

Re: Adding the differences of cumulative data in a column

Jihwan_Kim — Sun, 04 Dec 2022 04:23:19 GMT

Hi,

Does the table tell what is the sequence? Is there a column that indicates a sequence?

Please share your sample pbix file, or show all columns that are in the table (not just one column).

Or, you can make index column (like my sample) in Power Query Editor.

Thanks.

Re: Adding the differences of cumulative data in a column

upamanyu — Sun, 04 Dec 2022 06:16:56 GMT

I have sample table for two dates below. The values are collected at random times for a particular date. Using an index column is definitely needed as there are no time stamps. Also, there are null values in between the non blank values.

So, the addition of cumulative differences need to be returned per day.

Just a suggestion: is there a way to replace the blanks with the previous value until a new value arises?

Thank you so much Kim.

Date	Filled Product
15/8/2022	4632
15/8/2022
15/8/2022
15/8/2022
15/8/2022	4639
15/8/2022
15/8/2022
15/8/2022
15/8/2022	4640
15/8/2022	4642
15/8/2022
15/8/2022
15/8/2022
15/8/2022	0
15/8/2022
15/8/2022	25
15/8/2022
15/8/2022	45
15/8/2022
15/8/2022
15/8/2022	0
16/8/2022
16/8/2022
16/8/2022	25
16/8/2022
16/8/2022
16/8/2022
16/8/2022
16/8/2022	46
16/8/2022
16/8/2022
16/8/2022	0
16/8/2022
16/8/2022	100
16/8/2022	125

Re: Adding the differences of cumulative data in a column

upamanyu — Sun, 04 Dec 2022 06:26:49 GMT

The blank rows cannot be removed because there are other similar columns with values.

Re: Adding the differences of cumulative data in a column

Jihwan_Kim — Sun, 04 Dec 2022 08:35:10 GMT

Hi,

In my opinion, together with index column, it is possible to fill it with the prevous value.

Because the previous is defined by previous index number.

Thanks.