Forum Discussion

Pastrupgaard's avatar
Pastrupgaard
Helper I
7 years ago
Solved

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 mes...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

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's avatar
    Pastrupgaard
    Helper I
    7 years ago

    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