Forum Discussion
Cumulative Total
Below Dax is used for calculating the request on hand at the beginning of the selected period. I would like to calculate the cumulative total (example : Jan = 3, Feb = 2, March 4 then Jan = 3, Feb = 5 and March =9)for selected period(by month) in a matrix table. Table 1 has an active relationship with Valid date and inactive relationship with Decision date. Any assistance appreciated.
On Hand Cumulative =
CALCULATE(
COUNTX(
FILTER(
Table1,
Table1[ValidDate] < MIN('DimDate'[Date]) &&
(ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= MIN('DimDate'[Date])) &&
Table1[Type] IN {
"01", "02", "03", "04", "05", "06", "07", "08",
"09", "10", "11", "12", "13", "14", "15", "16",
"17", "18"
}
),
Table1[REFVAL]
),
CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)
)
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)
)
)
23 Replies
On Hand Cumulative Total =
VAR CurrentMonth = MAX('DimDate'[Date])
RETURN
SUMX(
FILTER(
ALL('DimDate'[Date]),
'DimDate'[Date] <= CurrentMonth
),
CALCULATE(
COUNTX(
FILTER(
Table1,
Table1[ValidDate] < 'DimDate'[Date] &&
(ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= 'DimDate'[Date]) &&
Table1[Type] IN {
"01", "02", "03", "04", "05", "06", "07", "08",
"09", "10", "11", "12", "13", "14", "15", "16",
"17", "18"
}
),
Table1[REFVAL]
),
CROSSFILTER('DimDate'[Date], Table1[ValidDate], None)
)
)Please mark my answer as complete
Thanks for your reply, I am getting this error - A single value for column 'Date' in table 'DimDate' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Libin7963. Please try this
FILTER(
Table1,
Table1[ValidDate] < SELECTEDVALUE('DimDate'[Date]) &&
(ISBLANK(Table1[DecisionDate]) || Table1[DecisionDate] >= SELECTEDVALUE('DimDate'[Date])) &&
Table1[Type] IN {
"01", "02", "03", ..., "18"
}
)
