Measure summing all values of the following 60min

[flyix]

Hello!

For a diagramm showing number of passengers vs. time of day I want to create a measure, that sums up all passenger numbers within the next 60min. So in the diagramm at f.e. 09:00, the curve will actually depict all passengers within 09:00-10:00.

(This is required to estimate short-term passenger load at any given time of day, rather than actual total number of passengers.)

	Flight Number	Time series	LocalDepTime	PaxShowUpTime	Seats
1	ABC123	15.07.2023	06:00	15.07.2023 04:00	100
2	ABC124	15.07.2023	06:15	15.07.2023 04:15	100
3	ABC125	15.07.2023	15:30	15.07.2023 13:00	100
4	DEF526	15.07.2023	07:00	15.07.2023 05:00	100
5	DEF527	15.07.2023	09:30	15.07.2023 08:30	100
6	FGH225	15.07.2023	06:25	15.07.2023 04:25	100
7	FGH996	15.07.2023	06:10	15.07.2023 04:10	100
8	TZE550	15.07.2023	07:05	15.07.2023 05:05	100
9	MFR753	15.07.2023	12:30	15.07.2023 10:30	100

The measure shall summarize Seats by PaxShowUpTime, so at 04:00 the measure should give 500 (Lines 1, 2, 4, 6, 7).

The complete dataset has values for multiple days and the diagram is already adjustable by a date slicer and a load factor slider.

[v-tangjie-msft]

Hi @flyix ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a table and measures. 

Measure = MAX('Table'[PaxShowUpTime])+TIME(0,60,0)

Table 2 = ADDCOLUMNS('Table',"60min",[Measure])

Measure 2 = CALCULATE(SUM('Table 2'[Seats]),FILTER(ALL('Table 2'),'Table 2'[PaxShowUpTime] >= MAX('Table 2'[PaxShowUpTime]) && 'Table 2'[PaxShowUpTime]<=MAX('Table 2'[60min])))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[flyix]

Hello Neeko,

thank you for your response! However your solution is not quite what I need. I attached the PBIX for you to have a look at, specifically the "Pax Departure" diagram is what I want to change.

Instead of showing how many passengers show up at what minute, it should show the number of passengers of the next 60min in 5min intervalls (starting at the min time of PaxShowUpTime, ending at the maximum time of PaxShowUpTime, or else just 03:00-23:00).

So the outcome probably will be a table like:

PaxShowUpTime	Sum of Seats including next 60min
03:00	623
03:05	655
03:10	697
...

(values are fictional)

https://we.tl/t-p5NyU1fWSK