Hi Folks, Am trying to calculate a simple rolling sum using the following dax but the result is just way off wrong
Rolling Sum (dollars) =
Var __Sum= SUMX(‘Inventory Balances Components -Report R5841003’,[Quantity On Hand (Dollars)]) + SUMX(‘V3411L - MRP Detailed Message Review’,[Quantity Available (Dollars)]) + SUMX(‘V4311CC - Open Orders’,‘V4311CC - Open Orders’[Open Orders (Dollars)]) - SUMX(‘V3412A- MRP Pegging Inquiry’,[Quantity Ordered (Dollars)])
VAR RollingSum = CALCULATE(
__Sum,
FILTER(
ALLSELECTED(DateTable),
DateTable[Date] <= MAX(DateTable[Date])
)
)
Return RollingSum
I added all the measures that are used for the rolling sum,
today should be 55,279 (40,962+0+14317-0) then 55,279-3092=52,187 but its just summing each month up.
All the columns in the addition are measures, could that be what is affecting this
This is driving me nuts, Plssss help
I am calculating rolling sum on other columns too and it works
Rolling Sum Qty W/O -ve inventory =
VAR RollingSum = CALCULATE(
[Sum of Quantities],
FILTER(
ALLSELECTED(DateTable),
DateTable[Date] <= MAX(DateTable[Date])
)
)
RETURN
IF(
RollingSum < 0,
0,
RollingSum
)
Have you tried out data mentor on this.
Perfect for this type of thing. I worked through a few iterations.
Thanks Sam but the running total calculation suggested didnt work. the date table exist
The rolling sum Dax calculation I have works now but something seems off when I roll it up for some reason to another category. on the Area bar chart, i noticed the roll up is still picking the intermediate calculations in the DAX.
.
This is what i did for clarity sake, I created a sum of quantities
Sum of Quantities = SUM(‘Inventory Balances Components -Report R5841003’[Quantity On Hand]) + SUM(‘V3411L - MRP Detailed Message Review’[Quantity_Available]) + SUM(‘V4311CC - Open Orders’[Open Orders]) - SUM(‘V3412A- MRP Pegging Inquiry’[Quantity Ordered]).
then calc the rolling sum as Rolling Sum Qty W/O -ve inventory =
VAR RollingSum = CALCULATE(
[Sum of Quantities],
FILTER(
ALLSELECTED(DateTable),
DateTable[Date] <= MAX(DateTable[Date])
)
)
RETURN
IF(
RollingSum < 0,
0,
RollingSum
)
what i expect to happen is as follows
But I am guessing this is what is happening
This behaviour might be correct or my Dax calculation is just totally off. Can someone point me in the right direction
