Reverse cumulative sum


#1

Hi
I am familiar with the standard cumulative pattern
The result of this pattern is that:
Jan = Jan
Feb = Jan + Feb
Mar = Jan + Feb + Mar

Dec = Jan + Feb + … + Dec

Is there a way that I can reverse this so that I can get the opposite result

I need to know what my sales were in reverse cumulative order. I have an entire year of sales data
so
Jan = Jan + Feb + Mar + … + Dec
Feb = Feb + Mar + … + Dec

Dec = Dec

Obviously I have just used months for simplicity of explanation. This would use a dates table

Thanks


#2

This is possible but you wouldn’t be able to use the standard cumulative total pattern I would say.

You will have to use an index column that is self generated somehow.

See below for the technique and patterns to use to get this working.


#3

Hi Sam,
I see what you are getting at here.
My example is probably a little simpler than this (or maybe a little more complex).

My fact table has

  • Salesman id
  • Date

All I am doing is a count of sales by month and doing a forward forecast to see who will be my most profitable salesman by the end of the year.

I do not need to go to the effort of creating an index such as you have. I can create the index directly on the fact table in query builder. This I have done by sorting by date and adding an index column. This gives me a convenient index column.

So now the fact table is

  • Salesman id
  • Date
  • Index

I probably don’t even need date anymore and could hide that. :grinning:

The question I now have is how do I “adapt” your formula from above to do cumulative using the index?

Thanks


#4

You know this is actually much easier than first thought, and I totally over thought it in the beginning

Check out the solution below

Reverse Cumulative Sales = 
CALCULATE( [Total Sales],
    FILTER( ALLSELECTED( Dates ),
        Dates[Date] >= MIN( Dates[Date] ) ) )

You actually only need to change one simple thing in the formula.

See if you can replicate.

Chrs


#5

Hi Sam,
Thanks for this. I think I can work with that.


#6

HI all
I am trying to figure out how to extend this one more step.
It works fine providing I don’t slice it by months, but if I add a slicer for months it breaks down.
What I am hoping to do is be able to slice it by months, but still see the reverse cumulative total to the end of the year.
For example, if I select Jun in the above example I still need to see $7m, but what actually happens is that it becomes $1M (that month’s sales).
It is probably a matter of modifying total sales to ignore the context of the date slicer but not quite sure how to make it happen.
Thanks


#7

Can you add images of the issues your seeing so that I can view the calculations in the correct context you are needing.

Thanks


#8

Hi Sam,
I have anonymised the data so I can attach the file as well. This may help.
Basically I am taking the last x years number of sales (not interested in total sales at this point, just the number of sales per sales person).
I have created a matrix for sales per month per gender for each sales level

The cumulative is working correctly when I have not sliced by month.

When I slice by month:

What I need is to have the month cumulative remain correct.

Please see attached file
Thanks
Testing.pbix (296.3 KB)


#9

I’m pretty sure this is what you need here

Reverse Cumulative Sales = 
CALCULATE( [Number of Sales over time],
    FILTER( ALL( Dates[Month Of Year] ), 
        Dates[Month Of Year] >= MIN( Dates[Month Of Year] )))

As you’re using the month of year as the context in this case then the original formula wasn’t evaluating correctly.

I made these changes and it seems to work well


#10

Yep!
It’s the little things.
That seems to do it.
Thanks for the awesome work!
:+1:


#11

Hi Sam,
Something I missed before (and should have seen it. Sorry)
The column totals are all wrong.
Normally the fix is easy.
I would do something like

Reverse Cumulative Sales 2 = 
VAR CumulativeSales = 
CALCULATE( 
    [Number of Sales over time],
    FILTER( 
        ALL( Dates[Month Of Year] ), // Use ALL rather than ALLSELECTED to allow for month filter exclusion
        Dates[Month Of Year] >= MIN( Dates[Month Of Year] ) 
    ) 
)

VAR SalesTotal = 
SUMMARIZE(Sales, Sales[Sales Gender], Sales[Salesman Level],
          "Cumulative Total", CumulativeSales)     

RETURN
IF( OR (HASONEVALUE('Gender Lookup'[Gender]), HASONEVALUE('Salesman Level'[Salesman Level])),
    CumulativeSales,
    SUMX(SalesTotal, [Cumulative Total])
)

However, I must me missing something here.
I have tried multiple different combinations of the HASONEVALUE parameters and still not getting the totals. They are either way off or just close.

Any ideas?

I have attached my new file.

Thanks
Testing.pbix (303.7 KB)


#12

This is actually a bit harder around the totals.

I’ve had to work on it for a while and have come up with a solution.

There’s a little more to it than you would think.

The reason is because of the context that comes from the slicer. That really messes with the totals.

What you need to do is actually create a different table for the slicer like so

image

With no relationship to the model

Then with the correct formula you can get this

Reverse Cumulative = 
VAR MonthFilter = ISFILTERED( 'Month of Year Filter'[Month Of Year] )
VAR FilteredMonth = SELECTEDVALUE( 'Month of Year Filter'[Month Of Year] )
VAR TotalCheck = HASONEVALUE( Dates[Month Of Year] )
VAR Cumulative = CALCULATE( [Number of Sales over time],
                    FILTER( ALL( Dates[Month Of Year] ),
                        Dates[Month Of Year] >= MIN( Dates[Month Of Year] ) ))

RETURN
IF( TotalCheck = FALSE(),
    MAXX( SUMMARIZE( Dates, Dates[Month Of Year], "CumulativeTotal", Cumulative ), [CumulativeTotal] ),
        IF( MonthFilter = FALSE(),
            Cumulative,
                IF( FilteredMonth = SELECTEDVALUE( Dates[Month Of Year] ), Cumulative, BLANK() ) ) )

So as you can see not as simple as initially.

Hopefully this works for you.

I’ve attached here also.

Testing Reverse Cumulative.pbix (300.0 KB)


#13

Wow Sam
You are right. This totally changes the dynamic of the model.
Given that this testing file is only a subset of my final model it is quite a task!
I will see what I can do with it. Well done!


#14

Hi Sam,
I have worked my way through this rather complicated formula and it is quite brilliant but has a bit of a shortcoming.

Firstly, the total is wrong. In this example, it should be 426, not 412.

The second thing is that I need to reference the result of this in other measures, but now since we have removed the month filter from the calculation, I am getting incorrect results.

As a rather crude example, say I need “Chosen Sales Values”

Chosen Sales values = [Reverse Cumulative]

If I put this in a table against sales levels I will only get the full total, not the total of the chosen levels as I do not have the correct filter context

See attached
Testing Reverse Cumulative.pbix (305.5 KB)


#15

hmmm I’m confused around the totals

Your earlier example at the top of the post also had 412? Has the total always been wrong?

It’s look correct to me at the moment just by looking through the numbers

image

Same as here

image

I’m working on the other question to see if there’s a better solution for this one


#16

Hi,
Yes. It has always been wrong. I had not noticed that earlier. A bit of an embarrassing oversight. I was nearly ready to present to client before I caught it. I had to pull back on that. As a sanity check I dumped the results into Excel to check them and noticed the error.


#17

How about using a different formula for this other calculation.

I’ve done a bit of testing and this is probably the best way to go.

Here’s the formula

Selected Total = 
CALCULATE( 
    CALCULATE( [Number of Sales over time],
        FILTER( ALL( Dates[Month Of Year] ), Dates[Month Of Year] >= MIN( Dates[Month Of Year] ) )),
             FILTER( ALL( Dates[Month Of Year] ), Dates[Month Of Year] = SELECTEDVALUE( 'Month of Year Filter'[Month Of Year] ) ) )

And calcs

Here’s an updated formula as well. I’ve attempted to simplify it.

Reverse Cumulative = 
VAR MonthFilter = ISFILTERED( 'Month of Year Filter'[Month Of Year] )
VAR FilteredMonth = SELECTEDVALUE( 'Month of Year Filter'[Month Of Year] )
VAR TotalCheck = ISFILTERED( Dates[Month Of Year] )
VAR Cumulative = CALCULATE( [Number of Sales over time], 
                        FILTER( ALL( Dates[Month Of Year] ), Dates[Month Of Year] >= MIN( Dates[Month Of Year] ) ))
VAR TotalsAbsolute = MAXX( SUMMARIZE( Dates, Dates[Month Of Year], "CumulativeTotal", Cumulative ), [CumulativeTotal] )

RETURN
IF( TotalCheck = FALSE(), TotalsAbsolute,
        IF( MonthFilter = FALSE(), Cumulative,
                IF( FilteredMonth = SELECTEDVALUE( Dates[Month Of Year] ), Cumulative, BLANK() ) ) )

Attached
Testing Reverse Cumulative.pbix (300.2 KB)


#18

Thanks Sam
I will try these.
To assist you I attached the spreadsheet to show you what I am trying to achieve
Testing.xlsx (77.5 KB)


#19

Power BI calculations are never wrong, so it has to be the data or some other filter causing this problem.

Can you isolate the issue?


#20

It is probably something with the way that the number of sales are calculated in the first place.
The data is in the spreadsheet I attached.
If I look at the spreadsheet the numbers are slightly different for the grand total
image

Power BI
image

Just noticed that the AUG and DEC column are wrong in Power BI
Testing Reverse Cumulative.pbix (302.1 KB)