Creating cumulative (running) total which responds to date slicer

[RG_HC]

Hi all,

I'm trying to create a running total against an Amount column by Close Date. I have the data filtered by Month (ie January) and have duplicate dates (have now added an Index column to try and offset this but not sure how to include it in the formula filter). The "Create Quick Measure" option isn't working as the data isn't cumulating.

Can anyone help me out with the syntax for this? Previous attempts have not worked as I don't completely understand how it is working (Power BI noob). I have it working thanks to a coworker in a different scenario, but haven't been able to get it to work with the date filters in this case (I've included this in the screenshot -- tried a few different formula variations on what is in the screenshot, including the one generated by the quick measure, but not luck)

Thanks in advance

[Ashish_Mathur]

Hi,

In your visual, the close dates are the same for all rows.  So the running total for each row should be 2965.017.  Ami correct?

[RG_HC]

Hi there, that's one of the problems. This data comes from a CRM; the salesperson has put 31/1/18 as the close date for all the sales. So I generated an Index column to try to use to differentiate each row but unsure how to implment it so the running total works.

In this case it should be:

Amount             Running Total

300,000             300,000

279,000             579,000

2,006,017          2,585,017

380,000             2,965,017

[Ashish_Mathur]

Hi,

In that case, how does one know the order in which the amount should appear.  So , why should the answer not be

279,000

579,000

959,000

2,965,017

How does one freeze on the order of the amount (which in turn will impact the running total).

Also, will you be filtering/slicing the data only by month or will there be other filters as well?

[RG_HC]

Lets try a slightly modified dataset to better explain the problem:

Date Close            Amount             Running Total

 22/01/18              300,000             300,000

 24/01/18              279,000             579,000

 31/01/18              2,006,017          2,585,017

 31/01/18              380,000             2,965,017

To me, the sort order of amount and therefore the running total, should be inherited from the sort order of the dataset, in this case Date Close I added the index to try and help with dates that duplicate (close on the same date). 

The problem is still the syntax to get this to cumulate, given that the data (as per the real life scenario) has been filtered to only show January 2018 sales closing, and also sales that have a probabiltiy >=60%.

So, the filters are Close Date and Probability (>=60%).

Appreciate your time

[v-yulgu-msft]

Hi @RG_HC,

Please try below measure:

RunningTotal =
CALCULATE (
    SUM ( 'CRM data'[Amount] ),
    FILTER (
        ALLSELECTED ( 'CRM data' ),
        'CRM data'[Index] <= MAX ( 'CRM data'[Index] )
    )
)

Best regards,

Yuliana Gu

[RG_HC]

Thank you Yuliana, very nearly there! The only problem I have now is that the Index column isn't quite working how I'd hoped. Looking at the next month out, I have:

Close Date        Amount            Running Total       Index

31/03/18          421,794             421,794                 25

31/03/18        3,500,000          3,921,794                 41

30/03/18          274,288           4,196,082                 46

Clearly, the last opportunity on the list, at Index 46, closes first of the list, but was added last in the CRM. 

I'd actually like the Index to follow the Close Date - ie Sort by Close Date, but when I try to do that in the table, I get an error - "We can't sort the "Close Date" column by "Index". There can't be more than one value in "Index" for the same value in "Close Date". Please choose a different column for sorting or update the data in "Index".

Not sure what this means. Index is a Power BI generated Index column and unique...?   

[v-yulgu-msft · Accepted solution]

Hi @RG_HC,

In Query Editor mode, please sort the [Close Date] field with ascending order first before adding the index column.

Best regards,

Yuliana Gu

[RG_HC]

Thanks Yuliana, 100% spot on. I've deleted the Index column, sorted by Close date and then added the Index column as the last step in my Query. Worked like a charm 🙂

[parry2k]

You can sort close by index becuase duplicate close date will have different index value.

You need to concatenate close date with index and then sort your close date with this concatenated field and I think that will do it.