Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get Fabric certified for FREE! Don't miss your chance! Learn more

Reply
lexalexa
New Member

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. 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@lexalexa,

 

You may refer to measure below.

Measure =
VAR d =
    SELECTEDVALUE ( Table1[Date] )
RETURN
    COUNTROWS ( FILTER ( Table2, Table2[Pick up date] >= d ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@lexalexa,

 

You may refer to measure below.

Measure =
VAR d =
    SELECTEDVALUE ( Table1[Date] )
RETURN
    COUNTROWS ( FILTER ( Table2, Table2[Pick up date] >= d ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Lind25
Resolver I
Resolver I

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.