The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
21 | |
19 | |
18 | |
18 | |
14 |
User | Count |
---|---|
36 | |
34 | |
20 | |
19 | |
15 |