Running Total Selecting Latest Datapoint for Each Unique Entity

[dregan]

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

[Anonymous]

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.

[dregan]

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. 

[Anonymous]

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.

[dregan]

Yes, it is the expected results.  I only showed Time - but I should have made it a DateTime.  These numbers update multiple times a day, but as time goes on I only want to add the Most Recent datapoint for each Load ID (in the same General ID).

[dregan]

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.   

[amitchandak]

@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])))