topic Re: Summing output of running total with itself in DAX Commands and Tips

Summing output of running total with itself

Anonymous — Wed, 13 Oct 2021 04:13:56 GMT

Hello guys,

I have a very complex case that I am presenting here. Need DAX for the following.

As we stand in the month of September, the formulas are given for the September and so on in row 'FC VOL CAL'

These formulas will then be carried forward in the same way when we stand in the month of October.

It is a lot to do with running total. I can get the total for SOH and IMS, but the running total of the running total of the subtraction (see, already confusing) is not quite happening, especially with the ZERO replacement as well.

		E	F	G	H	I	J	K
		sep	oct	nov	dec	jan	feb	mar
4	soh	0	22586	0	8927	2350	0	1404
	rt soh	0	22586	22586	31513	33863	33863	35267
6	ims	2684	3587	3542	4093	4452	3711	4033
	rt ims	2684	6271	9813	13906	18358	22069	26102
	fc vol	0	0	16315	0	1292	0	0
10	fc vol cal	=IF((E4-E6)<0,0,E4-E6)	=IF((E4-E6-E10)<0,0,E4-E6-E10)	=IF(((E4+F4)-(E6+F6)-(E10+F10))<0,0,(E4+F4)-(E6+F6)-(E10+F10))	=IF(((E4+F4+G4)-(E6+F6+G6)-(E10+F10+G10))<0,0,(E4+F4+G4)-(E6+F6+G6)-(E10+F10+G10))	=IF(((E4+F4+G4+H4)-(E10+F10+G10+H10))-(E6+F6+G6+H6)<0,0,(E4+F4+G4+H4)-(E10+F10+G10+H10)-(E6+F6+G6+H6))	0	0

Re: Summing output of running total with itself

lbendlin — Mon, 18 Oct 2021 21:58:54 GMT

Instead of all these IF functions use MAX

=IF((E4-E6)<0,0,E4-E6)

becomes

=MAX(E4-E6,0)

etc.

In order for your data to become usable you will want to unpivot it first. Like so (for example)

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZU+7DsIwDPwVlJkhdt4fwcJadSglFSBKUFvK72ObFqliyJ3t3PlRVepY3mqvDrkZX0OmaMxPwtJOhI8yE55zS3hrHoRdPhH2zaDqfaUs68uFUNNDdNEvcUwYuGScXipgtRWTo2SYdv++lQ04MMwm+g079EFasOraj+zx0cpXDEIWiaxOrLfWcWYCgBSNEXP4zt/4PQbWpCiDwSTNIyBSX1lM+yRi0Cg9uNq1u7nclxvkQk+brzEm/H3V9Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, #" .1" = _t, E = _t, F = _t, G = _t, H = _t, I = _t, J = _t, K = _t]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Row", "Measure"}, "Attribute", "Value") in #"Unpivoted Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".