Forum Discussion

fetster's avatar
fetster
Frequent Visitor
5 years ago
Solved

Calculate Age based on difference between 2 dates in seperate tables.

Hi. 

 

I have two tables:

Bookings:

Booking ID

Date Booked 

 

Tickets:

BookingID

TicketID

Name of Person

Date Of Birth

 

One booking can consist of many tickets. 

 

 

I am trying to calculate the age of the people in months on the date they have booked. 

 

I tried this calculation on a new column in the Tickets table:

Age = DATEDIFF('bb_api bookings'[selected_date], RELATED('bb_api tickets'[date_of_birth]),MONTH)
 
But I am getting this error : 
A single value for column 'selected_date' in table 'bb_api bookings' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
 
Any suggestions what i am doing wrong and how to achieve this please? 
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    fetster try this new column in the Tickets table:

     

    Age = DATEDIFF('bb_api tickets'[date_of_birth],RELATED('bb_api bookings'[booked_date]), MONTH)

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

1 Reply

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    fetster try this new column in the Tickets table:

     

    Age = DATEDIFF('bb_api tickets'[date_of_birth],RELATED('bb_api bookings'[booked_date]), MONTH)

     

    Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡