Forum Discussion

miinseon's avatar
miinseon
Icon for Helper I rankHelper I
2 years ago
Solved

table with more than one date column

Hi all 🙂


I want to ask a question about a table with more than one date column


My table has more than one date column.
I want to get a measure for each date column.
For example, if I have a reserve date and a payment date, I want to get a measure for the number of bookings and the amount paid in one screen.

 

I've included some data from the table below and an example of the screen I'd like to display.

 

I hope you can help me.
Thanks in advance

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi miinseon ,

     

    Your solution is great, bhanu_gautam . Here I have another idea in mind, and I would like to share it for reference.

     

    As per your question, I have created simple data :

     

     

     

     

    Since you need to get the amount paid one day after the reserve date, we use the following function:

     

     

     

     

    Measure 2 =

VAR _date = SELECTEDVALUE('DAX DateTable'[Date]) + 1

VAR _sum = CALCULATE(SUM('Table'[price]),FILTER(ALL('Table'),'Table'[payment_date] = _date && 'Table'[reserve_date] = SELECTEDVALUE('DAX DateTable'[Date])))

RETURN

_sum

     

     

     

     

    The final results are as follows:

     

     

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    miinseon , You can achieve this using Measure and date table

     

    First create measures
    CountBookingsByReserveDate = COUNTROWS('Bookings')

     

    TotalAmountPaidByPaymentDate = SUM('Bookings'[AmountPaid])

     

    Then create a date table for reserve date go to modelling and new table

    ReserveDateTable = CALENDAR(MIN('Bookings'[ReserveDate]), MAX('Bookings'[ReserveDate]))

    Similarly new table for Payment

     

    PaymentDateTable = CALENDAR(MIN('Bookings'[PaymentDate]), MAX('Bookings'[PaymentDate]))

     

    Create a relationship between Bookings[ReserveDate] and ReserveDateTable[Date].

    Create a relationship between Bookings[PaymentDate] and PaymentDateTable[Date].

     

    Then create visualizations

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi miinseon ,

     

    Your solution is great, bhanu_gautam . Here I have another idea in mind, and I would like to share it for reference.

     

    As per your question, I have created simple data :

     

     

     

     

    Since you need to get the amount paid one day after the reserve date, we use the following function:

     

     

     

     

    Measure 2 =

VAR _date = SELECTEDVALUE('DAX DateTable'[Date]) + 1

VAR _sum = CALCULATE(SUM('Table'[price]),FILTER(ALL('Table'),'Table'[payment_date] = _date && 'Table'[reserve_date] = SELECTEDVALUE('DAX DateTable'[Date])))

RETURN

_sum

     

     

     

     

    The final results are as follows:

     

     

     

    If you have any other questions please feel free to contact me.

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    2 years ago

    miinseon - To build on the idea from bhanu_gautam you can also do this with one date table. 

     

    Create base measures:

     

    CountBookings = COUNTROWS('Bookings')

TotalAmount = SUM('Bookings'[AmountPaid])

     

    Then create measures that reference these, but change the context of their calculation by date:

     

    CountBookingsByReserveDate = CALCULATE( [CountBookings], USERELATIONSHIP( 'Bookings'[Reserve Date], 'Calendar_table'[Date]))
TotalAmountPaidByReserveDate = CALCULATE( [TotalAmountPaid], USERELATIONSHIP( 'Bookings'[Reserve Date], 'Calendar_table'[Date]))
 
CountBookingsByPaymentDate = CALCULATE( [CountBookings], USERELATIONSHIP( 'Bookings'[Payment Date], 'Calendar_table'[Date]))
TotalAmountPaidByPaymentDate = CALCULATE( [TotalAmountPaid], USERELATIONSHIP( 'Bookings'[Payment Date], 'Calendar_table'[Date]))

     

    Finally these measures will only work once you have created 2 "In-active" relationships from the Calendar table to the respective dates in the Bookings table. Very important to make sure they are both In-active. 

     

    If this works for you, please mark as the solution.