New Table from existing table plus calculated data from another table

[roadcyclist]

Hi,

i have the following problem with 2 Tables:

Table 1 is a table with yearly dates, e.g.

Start        |   End

01/01/22 | 02/01/22

02/01/22 | 02/01/22

03/01/22 | 02/01/22

Table 2 consists of parking data

Entry                 | Exit                   |  Parkingarea | Tickets

01/01/22 12:00 | 01/01/22 12:00 | P1                 | 1

02/01/22 09:00 | 01/01/22 12:00 | P3                 | 1

01/01/22 07:15 | 01/01/22 12:00 | P1                 | 2

01/01/22 12:00 | 01/01/22 12:00 | P4                 | 1

So like to create a new table wicht consists of table 1 and calculated data from table2, like this

TableNew = SELECTCOLUMNS(table1, "Start" , [Start], "End" , [End], SUMMARIZECOLUMNS(table2, [tickets], filter(table2, table2[Parkingarea]="P1"), FILTER (ALLSELECTED(table2), table2[Entry] <= table1[start]
&& table2[Exit] >= table1[start])))

and this for all parkingareas (>25). Does anybody know a solution?

Thanks?

[Anonymous · Accepted solution]

Hi @roadcyclist,

You can try to use the following calculated table formula to create a new table based two table fields and conditions:

TableNew =
SELECTCOLUMNS (
    table1,
    "Start", [Start],
    "End", [End],
    "tickets",
        CALCULATE (
            SUM ( [tickets] ),
            FILTER (
                ALLSELECTED ( table2 ),
                table2[Parkingarea] = "P1"
                    && AND ( table2[Entry] <= [End], table2[Exit] >= [Start] )
            )
        )
)

Regards,

Xiaoxin Sheng