Running Total Showing Increase and Decrease

[leeleeds123]

Hi all,

I am trying to create a running total that goes up and down depending on whether a value has been updated at a later for a certain ID type. I have prepped some data below to give you an idea of what i am trying to achieve. 

As you can see, if the value for a given ID in a subsequent month is reduced the running total decreases by the difference between the value in the current month and it's previous value. 

I have tried a few different methods and i am going round in circles.

1. I have a data table linked to the date column in my fact table

2. i have checked all data format types

Any guidance on the best approach would be much appreciated.  I have tried to search the forum for similar challenges that were posed and i couldn't find anything that exactly matched what i needed to do. 

ID	Date	Risk Score	Running Total
1	01 Jan	15	15
1	01 Feb	10	10
2	05 Feb	5	15
1	01 Mar	14	19
2	05 Mar	4	18
2	01 Apr	8	22
1	01 May	14	22
1	01 Jun	18	26

[Ankur04]

Hi ,

I will still need some inputs. can you please help me understand by which logic you have arrived on below values which are 19 and 22

1	1-Mar	14	19
1	1-May	14	22

[leeleeds123]

Thanks Ankur, the reason it doesn't make sense on its own is becuase of the value changes for ID 2. If i show it just with a single value it might be simpler. 

Slightly harder to grasp with just the one ID but i am trying to pull the value for the ID for summary as it changes over time. I want my running total to go down as well as up.

ID	Date	Risk Score	Running Total
1	Jan	10	10
1	Feb	12	12
1	Apr	8	8
1	June	10	10

[Ankur04]

Hi,

I have sorted your sample data for understanding.

ID	Date	Risk Score	Running Total
1	1 Jan	15	15
1	1-Feb	10	10
1	1-Mar	14	19
1	1-May	14	22
1	1-Jun	18	26
2	5-Feb	5	15
2	5-Mar	4	18
2	1-Apr	8	22

can you please explain in details the logic for your running total calculation. as per the description provided above.

let's say for ID = 1, the value was 15 in Jan and in Feb it became 10 so running total become = 15 - (15-10) = 10 and in march the value is 14 then by which logic it became 19. please explain I may help you to achieve this.

[leeleeds123]

Hi Ankur, thank you for responding so quickly.  For the sake of understanding it would make sense to sort by ID for the whole data set) . What i am trying to achieve is a running total that has the ability to decrease if at a later the risk score with a matching ID decreases. 

For what i am trying to achieve i want to be able to combine this for multiple ID sets, so essentially if a new risk score is added in a given month then the whole of that score will be added to the running total, however if a risk has been modified (so there is an ID from an earlier date that  matches), then the running total will be updated by the difference in the two scores.