Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
Would really appreciate help to formulate the target outcome as shown below. We have list of bookings with arrival dates and booking dates. What we need to identify is how many bookings in percentage was booked in a given period. So for example in the screenshot below, 25% of bookings arriving in month of Jan was booked 6 months ago and beyond in context of month of arrival, 23% of it was booked between 4 and 6 months ago and so on. Of course the total is 100%, the percentage below are just random percentage I generated in excel just to give a visual represention of the goal. I've been searching solution as well as trying to come up with myself but I can't seem to figure it out (maybe my question in google is not on point) and I would really really appreciate any help I could right now to save me from my report day coming tomorrow. 🙂
Edit : I think I wasn't very clear on my question. So for example we need to answer the question of how many were booked in advance on a given period for bookings arriving on January 2021. So 6 months beyond are all bookings received on Jun 2020 and below arriving on January 2021. 4 - 6 months are bookings received between Jul2020 and Sep 2020. 2-3 months are bookings received between Oct2020 - Nov 2020. 1 month are bookings made on Dec 2020 and less than a month are bookings made on the same month which is Jan 2021.
Solved! Go to Solution.
Hi Justivan,
First, you have to create a dynamic "Booking Date Group" calculated column in the table using DAX, Once you derive this column, then you just need to use the DIVIDE function to get the required %.
Calculated column:
BookingDate_Group =
Appreciate the Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂
Regards,
N V Durga Prasad
Hi Justivan,
First, you have to create a dynamic "Booking Date Group" calculated column in the table using DAX, Once you derive this column, then you just need to use the DIVIDE function to get the required %.
Calculated column:
BookingDate_Group =
Appreciate the Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂
Regards,
N V Durga Prasad
Hi @nvprasad ,
Thank you for the feedback. Please correct me if I'm wrong but that solution above is relative to the current date right? Apologies but I think I wasn't clear enough on my question. I have edited my post and gave it a better explanation ( hopefully )
Hi,
Yes, Var firstdayofMonth = STARTOFMONTH(Calander[Date]) in this variable please use "Booking Date" and you are going to create a calculated column within your table.
Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂
Regards,
N V Durga Prasad
Hi @justivan
If you share a sample of the report it would be much faster for all the community to help you out.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Hi @aj1973 ,
I'm trying to prepare a sample data as my current data is connect to our database which contains all of our company's data.
Hello All,
I've come up with a solution by creating a calculated column that get's the difference of CreateDate and InDate of bookings with an interval of month.
Calculated Column
Column = DATEDIFF (Bookings[CreateDate], Bookings[InDate], MONTH )
Measures
Booking Count =
CALCULATE (
DISTINCTCOUNT ( Bookings[RefID] ),
FILTER (
Bookings,
Bookings[Status] <> "Can" -- remove cancelled bookings
), USERELATIONSHIP ( ArrDate[Date], Bookings[InDate] )
)
Within Current Month =
CALCULATE (
Bookings[Booking Count],
FILTER (
ALL (Bookings), Bookings[Column] = 0
), USERELATIONSHIP( Bookings[InDate], ArrDate[Date] )
)
1 Month Ago =
CALCULATE (
Bookings[Booking Count],
FILTER (
ALL (Bookings), Bookings[Column] = 1
), USERELATIONSHIP( Bookings[InDate], ArrDate[Date] )
)
2 - 3 Months Ago =
CALCULATE (
Bookings[Booking Count],
FILTER (
ALL (Bookings), Bookings[Column] >= 2 && Bookings[Column] <= 3
), USERELATIONSHIP( Bookings[InDate], ArrDate[Date] )
)
4 - 6 Months Ago =
CALCULATE (
Bookings[Booking Count],
FILTER (
ALL (Bookings), Bookings[Column] >= 4 && Bookings[Column] <= 6
), USERELATIONSHIP( Bookings[InDate], ArrDate[Date] )
)
More than 6 Months Ago =
CALCULATE (
Bookings[Booking Count],
FILTER (
ALL (Bookings), Bookings[Column] > 6
), USERELATIONSHIP( Bookings[InDate], ArrDate[Date] )
)
To be honest, I'm not sure if this is the correct way to do it. Now the next problem to solve is how to rotate this where the column should be the month of arrival and the rows are the measures. I will not be able to drill down by clients if I simply turn on 'Show on rows' option in the matrix.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |