How to find the Difference between running total

[Akshaymanjunath]

for example consider the below data

i already have calculated a formula for calculating the running total of sales for each month.

when this formula is used in bar chart it will display the cumulative total for each month

the cumulative total of each month is as follows: 

jan: 20

feb: 30

mar:90

apr:200

above are the mentioned running sum total till each month, now when previous months running sum is sutracted from current months running sum, for example 20-0 = 20, 30-20=10 , 90-30=60 ........

you should get the below result:

jan:20

feb: 10

march: 60 

apr: 110

is short the following operation to be performed as per each month by subtracting the current month running sum total with previous month running sum total and the difference of these running sums has to be displayed using a dax query :

the logic for solution required in dax for the calculation is displayed below:

jan:20 - 0 =20

feb:30 - jan:20 = 10

march:90 - feb:30 = 60

april: 200 - mar:90 = 110

@MattAllington @xifeng_L @lbendlin @vicky_  could you please help me achieve this

[Anonymous · Accepted solution]

Hi @Akshaymanjunath, Hello @lbendlin, @MFelix ,

Thank you for your prompt reply.

Your answer is excellent!

And I would like to share some additional solutions below:

We can first add a new index column for your “Month” column in Power Query for sorting,

Then using below syntax to create a new calculated column, mainly using MAXX and EARLIER function:

Column =

  VAR __Current = [Sum]

  VAR __PreviousDate = MAXX(FILTER('test1','test1'[Index] < EARLIER('test1'[Index])),[Index])

  VAR __Previous = MAXX(FILTER('test1',[Index]=__PreviousDate),[Sum])

RETURN

  __Current - __Previous

Result:

Information for your reference:

Add an index column - Power Query | Microsoft Learn

MAXX function (DAX) - DAX | Microsoft Learn

EARLIER function (DAX) - DAX | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

The running sum difference is the current value.

[MFelix]

Hi @Akshaymanjunath ,

Is this cumulative total a measure or a column?

You can try and create a measure similar to this:

Difference = [Running Sum Measure] -  CALCULATE([Running Sum Measure], DATEADD(Table[Date], -1, MONTH))

 In the above code I'm assuming you have a measure that does the sum (running sum measure)