Binning (sort of) time stamped values and summing

[etalon]

I have tried a few methods, and although I may have worked out a really convoluted way I could theoretically do this, it seems like it was not the way things are supposed to be done in Power BI (and would make visualisation really difficult).

The data is a list of tanks, "TankDB" (which includes various bits of tank related data), along with obviously the list of unique tank IDs.

TankDB:

TankID

0670-1

0694-1

0800-1

0804-1

ect…

A fuel level table, which has time/date stamped fuel levels, and the tank ID it was recorded against:

FuelLevels:

DateTime	Level	TankID
10/04/2019 09:49	1289	0670-1
07/05/2019 14:43	1350	0670-1
14/06/2019 09:57	1400	0670-1
01/07/2019 11:39	1400	0670-1
11/07/2019 08:57	1400	0670-1
16/07/2019 10:26	1310	0670-1
24/07/2019 10:59	1425	0670-1
07/05/2019 10:15	2200	0800-1
19/06/2019 08:02	1900	0800-1
26/06/2019 08:30	2020	0800-1
02/07/2019 09:56	2000	0800-1
10/07/2019 11:14	2000	0800-1
15/07/2019 07:07	2000	0800-1
18/07/2019 06:48	2000	0800-1
22/07/2019 07:37	1950	0800-1
26/07/2019 07:18	1900	0800-1
ect..	ect..	ect..

The particular output I am after is a table as follows:

SumLevels:

Week	Date	Sum Levels
1	06/01/2019	?
2	13/01/2019	?
3	20/01/2019	?
4	27/01/2019	?
5	03/02/2019	?
6	10/02/2019	?
7	17/02/2019	?
8	24/02/2019	?

The way I want the Sum Levels column to work: For each Tank ID in TankDB, find the latest fuel level record that does not exceed the date in Date column. Then, sum all of these found values together.  This will then result in a value which is the total quantity of fuel in the tanks up to a given date.

I can generate the table of dates OK I think, but I am stuck after this.

My non very non dax pseduocode which might explain my intentions:

foreach (tankID in tankDB){

   output=filter(FuelLevels, FuelLevels[DateTime]<SumLevels[Date],FuelLevels[TankID]=tankID)

  latestMatchingLevel=Mostrecent(output, [Level]

  sumFuel+=LatestMatchingOuput

}

Thankyou so much for any help!

[v-xuding-msft]

Hi @etalon ,

I created a measure to implement you can have a try.

Measure = CALCULATE(SUM(Table4[Level]),FILTER(Table4,Table4[DateTime] <= MAX(Table6[Date]) && Table4[DateTime] >= MAX(Table6[Date])-7 ))

The following is my sample you can download.

sample

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[etalon]

Sorry, this doesnt quite work as I am after.

What I am after (quoting my original post):

For each Tank ID in TankDB, find the latest fuel level record that does not exceed the date in Date column. Then, sum all of these found values together.  This will then result in a value which is the total quantity of fuel in the tanks up to a given date.

The measure you have suggested sums all the fuel level data in a particular week, and seems to also double count fuel levels (ie if there is two readings for tank XXX, it will add them both together).

What I want is a way to get all the most recent levels for each tank up to a particular cutoff date.

[etalon · Accepted solution]

OK, I gave up using Power BI to do this. I have done the data processing in C# to get the data how I want it.

[etalon]

Thankyou so much for this, 

I cant get the download to work for some reason (comming up with as a sharepoint error) will try it by ysing your measure and will let you know!