Forum Discussion
Cumulative problems
Hi,
I'm having some trouble with the cumunative formula.
The problem arises in running total (secured), which is made up off actuals + backlog. As you can see in Oct, Nov & Dec the total gets messed up because there is no actuals present. Running Total (secured) for October should be 36.878.936 instead of 6.640.742.
I'm using the below formulas:
Running Total (Actuals) =
CALCULATE(sum(f_sales[Actual ($)]);
filter(
ALLSELECTED(f_sales);
f_sales[date_key]<= MAX(f_sales[date_key])))
Running Total (Backlog) =
CALCULATE(sum(f_backlog[Backlog ($)]);
filter(
ALLSELECTED(f_backlog);
f_backlog[date_key]<= MAX(f_backlog[date_key])))
Running Total (Secured) =
[Running Total (Actuals)] + [Running Total (Backlog)]
The solution to the problem would be to have September number ($25.572.537) repeated in Oct, Nov & Dec.
I appreciate any help!
Br,
Astrupgaard
Im not sure of this but it seems your sales and backlog dates are different (differenct ranges). Do they go from month 1 to month 12 or just the months with sales or backlog?
What happens if you use f_month_no_cob in the filtering instead of date keys? It should then sum over the entire year and repeat numbers when there is nothing to sum.
Br,
T
4 Replies
Im not sure of this but it seems your sales and backlog dates are different (differenct ranges). Do they go from month 1 to month 12 or just the months with sales or backlog?
What happens if you use f_month_no_cob in the filtering instead of date keys? It should then sum over the entire year and repeat numbers when there is nothing to sum.
Br,
T
Thank you t_R,
The problem was the different date ranges. When using f_month_no_cob I got the right answerBr,
Pastrupgaard
Hi Pastrupgaard,
I updated your formula like this, it should work well now.
Running Total (Actuals2) = VAR total = CALCULATE ( SUM ( f_sales[Actual ($)] ), FILTER ( ALLSELECTED ( f_sales ), f_sales[date_key] <= MAX ( f_sales[date_key] ) ) ) RETURN IF ( ISBLANK ( total ), CALCULATE ( SUM ( f_sales[Actual ($)] ), ALL ( f_sales ) ), total )Here is the result for your reference.
For more details, please check the pbix as attached.
https://www.dropbox.com/s/wl0lz9dcf646ssf/Cumulative%20problems.pbix?dl=0
Regards,
Frank
Hi Frank
Your solution could also have worked. The only problem was i have more sales data than 2018, so the number i got was too high. If I contrained it to 2018 then it would work perfectly. Thank you for your input!!!!
Br,
Pastrupgaard
