Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 |
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 |
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.
The blank rows cannot be removed because there are other similar columns with values.
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] )
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.
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.
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.
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
9 | |
8 |