cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 4
Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Helper I

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

Anonymous
Not applicable

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

Helper I

Thank you t_R,

The problem was the different date ranges. When using f_month_no_cob I got the right answer

Br,
Pastrupgaard

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.