Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
Sign up nowGet Fabric certified for FREE! Don't miss your chance! Learn more
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.
Solved! Go to Solution.
You may refer to measure below.
Measure =
VAR d =
SELECTEDVALUE ( Table1[Date] )
RETURN
COUNTROWS ( FILTER ( Table2, Table2[Pick up date] >= d ) )
You may refer to measure below.
Measure =
VAR d =
SELECTEDVALUE ( Table1[Date] )
RETURN
COUNTROWS ( FILTER ( Table2, Table2[Pick up date] >= d ) )
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.
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.
| User | Count |
|---|---|
| 62 | |
| 62 | |
| 42 | |
| 21 | |
| 18 |
| User | Count |
|---|---|
| 120 | |
| 113 | |
| 38 | |
| 29 | |
| 28 |