Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |