cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## How to find the Difference between running total

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

1 ACCEPTED SOLUTION
Community Support

Hi @Akshaymanjunath, Hello @lbendlin@MFelix ,

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:

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.

3 REPLIES 3
Community Support

Hi @Akshaymanjunath, Hello @lbendlin@MFelix ,

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:

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.

Super User

The running sum difference is the current value.

Super User

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)

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.