Please help before I jump off a bridge :( I need to do cumulative total

[BabyBinki821]

Hi

I have spent a day on this and going crazy.

I need to create cumulative sum of accounts and put it in week end and should look like the graph down below. The other part of this is that I cannot have the graph reset to 0 to whatever date is picked in the splicer as the earliest. In terms of my data table I have accounts, when they made their first order and have a date table that week end. I am utterly lost can't even get the column to sum up (created a new column and put the value 1 in there).

[BabyBinki821]

Do you know how I would put this in a column so I can % growth and other calculations?

[tamerj1]

@BabyBinki821 

The calculated column cannot interact with  the filter context. Can you please explain what exactly do you emeant to achieve?

[BabyBinki821]

A lot of the charts that I need to use have two main variables - cumulative accounts & orders with week end as the main time function. So a couple of things I need to do show cumulative account growth week over week, show % of cumulative accounts by region over time, show weekly average of orders/cumulative accounts for that week. So in my head I though if I had a column with the cumlative values it would be easy to do some of those charts below that I already have made in Excel.

[tamerj1 · Accepted solution]

Hi @BabyBinki821 

Please don't!

At least try this before 😅
I assume you are using Date[End of Week] column in you visual and the Date[Date] column is in the slicer. Then you may try

Cumulate Ordering Accounts =
VAR FirstDateInSlicer =
    MIN ( 'Date'[Date] )
VAR LastDateInFiltet =
    MAX ( 'Date'[Date] )
VAR CurrentDateInFilter =
    MAX ( 'Date'[Last Date of Week] )
RETURN
    CALCULATE (
        SUM ( Accounts[Assigned Value] ),
        ALLEXCEPT ( 'Date', 'Date'[Date] ),
        Accounts[FIRST ORDER] >= CurrentDateInFilter
    )

[tamerj1]

@BabyBinki821 

Also I forgot to mention that this a measure not a calculated column

[BabyBinki821]

This worked!!! But now I have balnk in all my time splicers 🤣

[tamerj1]

@BabyBinki821 

Can you please share some screenshots of you data and data model as well as the report and slicers

[BabyBinki821]

You are a life saver! The blanks were caused from how I had the relationship set with the data tables. I finally figured it out how to fix it.

But you sir saved my life.