Forum Discussion
Issues with Running Total when Entry is blank
Hello everyone, I've been melting my head arround this one and i've tried multiple approaches and non of them seem to be working.
ContinuousSUM_GMV =
VAR ContinuousSUM =
CALCULATE(
SUM('table'[Revenue]),
FILTER(
ALL('table'),
'table'[date_diff_imp_sales] <= MAX('table'[date_diff_imp_sales])
)
)
RETURN ContinuousSUM
It works up until a point. my date_diff_imp_sales goes from 0 to 14. On the day 0 my revenue is what it shows on the image and on the day 8 my gmv goes back to the values on day 0 but my running gmv should still be equal to day 7 but its assuming the value from day 0. I've tried couple of approaches but nothing seems to work.
Date_Diff_Imp_Sales is My Dimension
Revenue is Revenue per Date_Diff_Imp_Sales
% Sales is % based on grand total of column
% SUM and Continous is DAX Formula.
Any tips?
Bruno_Soares_25
You model is not clear to me, if you have a separate dates table , why you have named it here as table for both fact and dimension? I am also aware of other fileds added in the visual.
If you have a dates table then the pattern should be:ContinuousSUM_GMV = VAR MaxDate = MAX ( 'dates'[date_diff_imp_sales] ) VAR ContinuousSUM = CALCULATE ( SUM ( 'table'[Revenue] ), 'dates'[date_diff_imp_sales] <= MaxDate ) RETURN ContinuousSUM
2 Replies
Bruno_Soares_25
You model is not clear to me, if you have a separate dates table , why you have named it here as table for both fact and dimension? I am also aware of other fileds added in the visual.
If you have a dates table then the pattern should be:ContinuousSUM_GMV = VAR MaxDate = MAX ( 'dates'[date_diff_imp_sales] ) VAR ContinuousSUM = CALCULATE ( SUM ( 'table'[Revenue] ), 'dates'[date_diff_imp_sales] <= MaxDate ) RETURN ContinuousSUMalthough your tables and the relation between them are not clear. but based on my understanding you might have a date table where the column number f days come from. So you might need to use All function on your date table.
if not, please clarify or upload a sample dummy file.
If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
