cancel
Showing results for
Did you mean:
Helper II

## Calculate percentage of no. of bookings per month based on booking date

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.

1 ACCEPTED SOLUTION
Solution Specialist

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 =

Var firstdayofMonth = STARTOFMONTH(Calander[Date])
Var CurrentMonthFirstday = EOMONTH(TODAY(),-1)+1
Var MonthDifference = DATEDIFF(CurrentMonthFirstday,firstdayofMonth,MONTH)

Return SWITCH(TRUE(),
MonthDifference <=-6, ">6 Months",
MonthDifference >-6 && MonthDifference <=-4, "4 - 6 Months Ago",
MonthDifference >-4 && MonthDifference <=-2, "2 - 4 Months Ago",
MonthDifference >-2 && MonthDifference <=-1, "1 - 2 Months Ago",
"Less then a month ago")

Ratio:

=DIVIDE (COUNTROWS(TABLENAME), CALCULATE(COUNTROWS(TABLENAME),ALL(TABLENAME))

Appreciate the Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,

6 REPLIES 6
Solution Specialist

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 =

Var firstdayofMonth = STARTOFMONTH(Calander[Date])
Var CurrentMonthFirstday = EOMONTH(TODAY(),-1)+1
Var MonthDifference = DATEDIFF(CurrentMonthFirstday,firstdayofMonth,MONTH)

Return SWITCH(TRUE(),
MonthDifference <=-6, ">6 Months",
MonthDifference >-6 && MonthDifference <=-4, "4 - 6 Months Ago",
MonthDifference >-4 && MonthDifference <=-2, "2 - 4 Months Ago",
MonthDifference >-2 && MonthDifference <=-1, "1 - 2 Months Ago",
"Less then a month ago")

Ratio:

=DIVIDE (COUNTROWS(TABLENAME), CALCULATE(COUNTROWS(TABLENAME),ALL(TABLENAME))

Appreciate the Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,

Helper II

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 )

Solution Specialist

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,

Community Champion

If you share a sample of the report it would be much faster for all the community to help you out.

Regards
Amine Jerbi

and you can follow me on

Helper II

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.

Helper II

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.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors