cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justivan
Helper II
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. 🙂

justivan_1-1626611436783.png

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
nvprasad
Solution Specialist
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,
N V Durga Prasad

 
 

View solution in original post

6 REPLIES 6
nvprasad
Solution Specialist
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,
N V Durga Prasad

 
 

View solution in original post

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 )

nvprasad
Solution Specialist
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,
N V Durga Prasad

 

 

 

aj1973
Community Champion
Community Champion

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.

justivan_1-1626616202706.png

 

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Top Solution Authors