Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Instead of all these IF functions use MAX
so
=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".
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |