Skip to main content
cancel
Showing results for 
Search instead 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

Reply
leeleeds123
Regular Visitor

Running Total Showing Increase and Decrease

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. 

 

IDDateRisk ScoreRunning Total
101 Jan 1515
101 Feb1010
205 Feb515
101 Mar1419
205 Mar418
201 Apr822
101 May1422
101 Jun1826
4 REPLIES 4
Ankur04
Resolver I
Resolver I

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

 

11-Mar1419
11-May1422

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.

 

 

IDDateRisk ScoreRunning Total
1Jan1010
1Feb1212
1Apr88
1June1010
Ankur04
Resolver I
Resolver I

Hi,

 

I have sorted your sample data for understanding.

IDDateRisk ScoreRunning Total
11 Jan 1515
11-Feb1010
11-Mar1419
11-May1422
11-Jun1826
25-Feb515
25-Mar418
21-Apr822

 

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.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors