sum if

[lexalexa]

Hi,

I have two tables. One has list of unique dates. The other one has bookings each with a pick up date against it. In my first table I am trying to count how many bookings there are in the other table that are with a date equal or greater than the one in my first table.

so eg I want to calculate upcoming bookings so that I have:

Table 1

Date                Upcoming bookings

01/01/2018     3

02/01/2018     2

03/01/2018     2

Table 2

Booking ID      Pick up date

111                  01/01/2018

112                  04/01/2018

113                  04/01/2018

I am really struggling to write a measure that would let me do this. 

[v-chuncz-msft · Accepted solution]

@lexalexa,

You may refer to measure below.

Measure =
VAR d =
    SELECTEDVALUE ( Table1[Date] )
RETURN
    COUNTROWS ( FILTER ( Table2, Table2[Pick up date] >= d ) )

[Lind25]

This should do the trick.

Upcoming bookings = CALCULATE(
    COUNTROWS(Table2),
    All(Table1),
    DATESBETWEEN(Table2[Pick up date],FIRSTDATE(Table1[Date]),BLANK())
    )

This assumes you have related Table1 and Table2. Then add the measure and the date column from Table1 to a table visualisation.