Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 Sage
Solution Sage

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 Sage
Solution Sage

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

 
 

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

 

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.