Forum Discussion
sum if
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.
You may refer to measure below.
Measure = VAR d = SELECTEDVALUE ( Table1[Date] ) RETURN COUNTROWS ( FILTER ( Table2, Table2[Pick up date] >= d ) )
2 Replies
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.
You may refer to measure below.
Measure = VAR d = SELECTEDVALUE ( Table1[Date] ) RETURN COUNTROWS ( FILTER ( Table2, Table2[Pick up date] >= d ) )
