Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
Solved! Go to Solution.
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
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
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 answer
Br,
Pastrupgaard