CODEWITHSUNDEEP
powerbidax
umakant
umakant

Reputation: 49

how to get running sum/cumulative sum of a measure in power bi using DAX (direct query)

i have simple dataset like below where the delta is a measure (not a column) which is the diff of demand and supply.I wanted to calculated the running sum of measure "delta" as shown below.running sum needs to be at material-location-week level.

dax i tried: 

Cum =
var mat = MAX('table'[Material])
var pla = MAX('table'[Plant])
var pw =MAX('table'[PWk])
return
CALCULATE(
table[delta],
FILTER(
ALL(table),
'table'[Material]= mat && 'table'[Plant] <= pla && 'table'[PWk]<=pw
)
))
<>

material  location  week   demand   supply   delta(demand-supply)     running_sum??
123       1000      wk1        100    40                 60                   60
123       1000      wk2         40     30                 10                   70
123       2000      wk1         30     20                 10                   10
123       2000      wk2         40     15                 25                   35


please help. I am stuck with this and dont know`enter code here` where i am going wrong.

Upvotes: 0

Views: 1557

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

Perhaps:

running_sum =
VAR Mat =
    MAX ( 'Table'[material] )
VAR Loc =
    MAX ( 'Table'[location] )
VAR Wk =
    MAX ( 'Table'[week] )
RETURN
    CALCULATE (
        [delta],
        FILTER (
            ALL ( 'Table' ),
            'Table'[material] = Mat
                && 'Table'[location] = Loc
                && 0 + SUBSTITUTE ( 'Table'[week], "wk", "" )
                    <= 0 + SUBSTITUTE ( Wk, "wk", "" )
        )
    )

Upvotes: 0

Related Questions