Sum of data between two dates in another table

[Heinet]

Hi

I have 2 tables, simplified like this:

Table 1:

Incident	Datapoint	From time	To time
1	A	01.01.2022 12:00	01.01.2022 14:00
2	A	01.01.2022 22:00	01.02.2022.03:00
3	B	02.02.2022 03:00	02.02.2022 05:00
4	C	03.02.2022 15:00	04.02.2022 13:00

And Table 2

From time	To Time	Amount	Datapoint
01.01.2022 01:00	01.01.2022 02:00	40	A
01.01.2022 02:00	01.01.2022 03:00	36	A
01.01.2022 03:00	01.01.2022 04:00	12	A
01.01.2022 04:00	01.01.2022 05:00	16	A

And the table continues with amount for every 1h intervall for all datapoints.

Then my goal is to sum amount for each incident (in the first table) filters that sums amount with filters Table 2 (From time) <= Table 1 (From time), Table 2 (To Time) >= Table 1 (To time) and Table 1 (Datapoint) = Table 2 (Datapoint)

I tried with a simple Calculate(sum Table 2 Amount), Filter(Table 2, Table 2 (From time) <= Table 1 (From time) && Table 2 (To Time) >= Table 1 (To time) && Table 1 (Datapoint) = Table 2 (Datapoint)) but that didn't work. Might be some simple solution that I just can't see so would really appriciate the support

[Anonymous · Accepted solution]

Hi @Heinet ,

Please try to create measure with below dax formula:

Measure =
VAR cur_int =
    SELECTEDVALUE ( Table1[Incident] )
VAR cur_ft =
    SELECTEDVALUE ( Table1[From time] )
VAR cur_tt =
    SELECTEDVALUE ( Table1[To time] )
VAR cur_dp =
    SELECTEDVALUE ( Table1[Datapoint] )
VAR tmp =
    FILTER (
        ALL ( Table2 ),
        Table2[From time] <= cur_ft
            && Table2[To Time] >= cur_tt
            && Table2[Datapoint] = cur_dp
    )
RETURN
    SUMX ( tmp, [Amount] )

Add a table visual with Table1 fields and above measure

The measure is empty because there is no data to satisfy the condition.

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.