cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MattGell
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
CC182
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

sparse-coder
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

sparse-coder
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
sparse-coder
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

sparse-coder
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

amitchandak
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.

CC182
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

MattGell
Frequent Visitor

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

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