Forum Discussion
Cumulative Total
Hi Libin7963 ,
Once try this:
On Hand Cumulative Total :=
VAR CurrentDate = MAX('DimDate'[Date])
RETURN
SUMX(
FILTER(
ALL('DimDate'),
'DimDate'[Date] <= CurrentDate
),
VAR LoopDate = 'DimDate'[Date]
RETURN
CALCULATE(
COUNTROWS(
FILTER(
Table1,
Table1[ValidDate] < LoopDate &&
(
ISBLANK(Table1[DecisionDate]) ||
Table1[DecisionDate] >= LoopDate
) &&
Table1[Type] IN {
"01", "02", ..., "18"
}
)
),
CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)
)
)
On Hand Cumulative Total =
VAR CurrentDate = MAX('DimDate'[Date])
-- Pre-filtered table
VAR FilteredBase =
FILTER(
Table1,
Table1[Type] IN {
"01", "02", "03", "04", "05", "06", "07", "08", "09",
"10", "11", "12", "13", "14", "15", "16", "17", "18"
}
)
RETURN
SUMX(
FILTER(
ALL('DimDate'),
'DimDate'[Date] <= CurrentDate
),
VAR LoopDate = 'DimDate'[Date]
RETURN
CALCULATE(
COUNTROWS(
FILTER(
FilteredBase,
FilteredBase[ValidDate] < LoopDate &&
(
ISBLANK(FilteredBase[DecisionDate]) ||
FilteredBase[DecisionDate] >= LoopDate
)
)
),
CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)
)
)
Below table shows the on hand non cumulative for my visual
but the cumulative visual is showing below numbers. I think it is not filtering properly