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)
)
)
Hi Libin7963
Based on your error message, I would make sure the column 'Table1'[REFVAL] is a numeric datatype.
It is text datatype which looks like 200/255/FNC . I am trying to find the count of rows and not total
Hi Libin7963 ,
Try to Replace the measure with Countx and check if it works.No, that didn't work. My data is like this and it is the Case count cumulative is what I am not getting
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)
)
)Hi Libin7963 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.