cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Next future travel reservation per person

Hello,

 

can anybody please help with something I know is simple but I'm fairly new to DAX and I'm not finding it very intuitive.

 

I have 2 tables table1 called "AVI_USER" with people with unique "UserID" as primary key. Table 2 is for reservations for those people called "AVI_RES_PAX". the start dates of the reservations are stored in "AVI_RES_PAX[REQ_FROM_DT]". Each person could have many reservations into the future but I just want the next one (>TODAY) for each transport mode.

Reservations can be for different modes of transport as distinguished in "AVI_RES_PAX[REQ_MODE]". (Fixed Wing or Rotary Wing)

I simply want columns for the next reservation date for each mode per person and columns to show the origin and destination.

Desired result below:

UserID    NextFixedWingDate Origin Destination NextRotaryWingDate Origin Destination

NM001     01-Sep-20              LHR     GIG               31-Aug-20                FTS      ABZ

NM002     10-Oct-20              CDG    HOU              01-Nov-20               PPA     ABZ

NM002     20-Aug-20             LOS     JOB                                                

 

I know it looks simple but I just cant get it

Please help

Matt

4 ACCEPTED SOLUTIONS
New Member

Hi, 

 

1. Create a new column in Table 2: "Future Date" = if([Reservation start date] - TODAY()>0,"Y","N")

2. Create a Matrix visualization as follows: 

- Rows: UserID

- Columns: Mode

- Values: 'Earliest' Reservation start date --> 'First' Origin --> First 'Destination

- FIlters: (i) Future Date = Y; (ii) Top 1 UserID by Earliest Reservation start date

 

Hope that was what you were looking for!

View solution in original post

Resolver III
Resolver III

@MattGell  Create a measure as shown below.


future-travel reservation.PNG

The measure above shows how to calculate date for a user having fixed wing mode. You can alter at line 8 and create a new measure for other mode.

Similarly you can create measure for calculating Origin and Destination by tweaking at line 8 and 10. 

 

To test this go to report pane and choose table visual, drag AVI_USER[UserId] column and this measure.

 

Hope this helps you. If you find this helpful and working please mark it as solution.

 

Thanks 

 

 

View solution in original post

Resolver III
Resolver III

@MattGell  Create a measure as shown below.

future-travel reservation.PNG

The measure above shows how to calculate date for a user having fixed wing mode. You can alter at line 8 and create a new measure for other mode.

Similarly you can create measure for calculating Origin and Destination by tweaking at line 8 and 10. 

 

Hope this helps you. If you find this helpful and working please mark it as solution.

 

Thanks 

 

 

View solution in original post

Nailed it! Absolutely perfect.

Thank you so much

Matt

View solution in original post

8 REPLIES 8
Resolver III
Resolver III

@MattGell  Create a measure as shown below.

future-travel reservation.PNG

The measure above shows how to calculate date for a user having fixed wing mode. You can alter at line 8 and create a new measure for other mode.

Similarly you can create measure for calculating Origin and Destination by tweaking at line 8 and 10. 

 

Hope this helps you. If you find this helpful and working please mark it as solution.

 

Thanks 

 

 

View solution in original post

Resolver III
Resolver III

@MattGell  Create a measure as shown below.


future-travel reservation.PNG

The measure above shows how to calculate date for a user having fixed wing mode. You can alter at line 8 and create a new measure for other mode.

Similarly you can create measure for calculating Origin and Destination by tweaking at line 8 and 10. 

 

To test this go to report pane and choose table visual, drag AVI_USER[UserId] column and this measure.

 

Hope this helps you. If you find this helpful and working please mark it as solution.

 

Thanks 

 

 

View solution in original post

This is EXACTLY what I was looking for! works perfectly. Thank you so much

Matt

Nailed it! Absolutely perfect.

Thank you so much

Matt

View solution in original post

Super User IV
Super User IV

@MattGell , Try new columns like

next date = =minx(filter(table,table[UserID] = earlier([UserID]) && [NextFixedWingDate] >earlier([NextFixedWingDate])),[NextFixedWingDate])
next Origin = =minx(filter(table,table[UserID] = earlier([UserID]) && [NextFixedWingDate] =earlier([next date])),[Origin])
next Destination = =minx(filter(table,table[UserID] = earlier([UserID]) && [NextFixedWingDate] =earlier([next date])),[Destination])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

This helped a lot thank you, but I preferred using a measure that Sparse Coder suggested.

New Member

Hi, 

 

1. Create a new column in Table 2: "Future Date" = if([Reservation start date] - TODAY()>0,"Y","N")

2. Create a Matrix visualization as follows: 

- Rows: UserID

- Columns: Mode

- Values: 'Earliest' Reservation start date --> 'First' Origin --> First 'Destination

- FIlters: (i) Future Date = Y; (ii) Top 1 UserID by Earliest Reservation start date

 

Hope that was what you were looking for!

View solution in original post

Thats what I needed but not as a stand alone matrix - need those columns in another table, but its an awesome alternative that I will use in other reports.

Thank you so much.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors