Cumulative problems

[Pastrupgaard]

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

[v-frfei-msft]

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

[Pastrupgaard]

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 · Accepted solution]

@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

[Pastrupgaard]

Thank you t_R,

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

Br,
Pastrupgaard