Forum Discussion
Running Total Selecting Latest Datapoint for Each Unique Entity
Hi! I'm trying to come up with a way to have a running total for our inventories that have both a General ID and multiple Unique Load IDs. The inventory of these unique load IDs change independently, so when I am calculating a running total, I am looking to look at the MOST RECENT data point that is less than or equal to the current datetime for EACH unique Load ID that has the same general load ID. I made some example data below. In my actual data source there are also multiple General IDs.
I've tried many different combinations of SUMX and CALCULATE, but none seem to work...
| General ID | Load ID | Qty in Load | Time | Running total |
| 123 | 1 | 200 | 1:00 | 650 |
| 123 | 2 | 200 | 1:00 | 650 |
| 123 | 3 | 150 | 1:00 | 650 |
| 123 | 4 | 100 | 1:00 | 650 |
| 123 | 1 | 150 | 2:00 | 450 |
| 123 | 2 | 150 | 2:00 | 450 |
| 123 | 3 | 100 | 2:00 | 450 |
| 123 | 4 | 50 | 2:00 | 450 |
| 123 | 1 | 100 | 3:00 | 250 |
| 123 | 2 | 100 | 3:00 | 250 |
| 123 | 3 | 50 | 3:00 | 250 |
| 123 | 4 | 0 | 3:00 | 250 |
| 123 | 1 | 50 | 4:00 | 450 |
| 123 | 2 | 100 | 4:00 | 450 |
| 123 | 3 | 50 | 4:00 | 450 |
| 123 | 5 | 250 | 4:00 | 450 |
| 123 | 1 | 50 | 5:00 | 625 |
| 123 | 2 | 75 | 5:00 | 625 |
| 123 | 3 | 300 | 5:00 | 625 |
| 123 | 4 | 200 | 5:00 | 625 |
6 Replies
- amitchandak
Super User
dregan , calculate(sumx(values(Table[ Load Time]) , calculate(lastnonblankvalue(Table[Load ID]), Sum(Table[Qty in Load]))), Filter(allselected(Table), [Load Time] <= max(Table[Load Time])))
I think I also need a filter in there for the General ID, as there are a handful of those too, and I want to add all of the Load IDs that are the same General ID.
Hi dregan ,
Your running total means get the sum group by time, right?
You can create a measure wich is dynamic,
Measure = CALCULATE(SUM('Table'[Qty in Load]),FILTER(ALLSELECTED('Table'),[General ID]=MAX('Table'[General ID])&&[Time]=MAX('Table'[Time])))Dynamic results can be obtained by filtering by Load ID.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I don't want to filter by Load ID, unfortunately. I was the Sum of all the MOST RECENT datapoints for each load ID that are filtered by the General ID (as compared to the datetime value).
i.e. if load 1 has 200 on it on 3/1, the total on 3/1 will inclue that 200. But if on 3/2 the load drops to 150, he total on 3/2 will only include the 150 and NOT the 200.
Hi dregan ,
Your Running total in the sample data is not the expected result, is it?
Can you refine your sample data to provide corresponding expected results? The example you provided in the lateset reply includes dates, but there are no dates in the sample data, which confuses me.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
