Booking Pace

[ninakarsa]

Hi Support,

I have data which gives the date the customer will arrive in the car park and the date the booking was made. I want to use dax to create table B, which shows by booking date the number of customers arriving my month. Once this is calculated I need to find a way to calculate the 'booking pace', so that i can predict the total number of customers arriving by month. 

Table A:

Booking Date	Arrival Date of Customer	month Arriving date
01/01/2019	01/05/2019	5
01/01/2019	02/05/2019	5
01/01/2019	03/06/2019	6
01/01/2019	04/06/2019	6
01/01/2019	05/05/2019	5
01/01/2019	06/04/2019	4
02/01/2019	10/04/2019	4
02/01/2019	25/04/2019	4
02/01/2019	10/05/2019	5
02/01/2019	25/05/2019	5
02/01/2019	09/06/2019	6
02/01/2019	24/06/2019	6
02/01/2019	09/07/2019	7
02/01/2019	24/07/2019	7
02/01/2019	08/08/2019	8
02/01/2019	23/08/2019	8

Table B:

Booking Date	no Customers	Jan	Feb	Mar	Apr	May	Jun	July	Aug
01/01/2019	6				4	3	1
02/01/2019	10				2	2	2	2	2
03/01/2019	15	5		5			5
04/01/2019	10			5			5
05/01/2019	10			2	2	2	2	2
06/01/2019	20		2	2	2	2	2	10
07/01/2019	15				5		5		5
08/01/2019	10								10
09/01/2019	20			2	4	4	5		5
10/01/2019	5				1	1	3

[ninakarsa]

or maybe another way which i can see if my bookings will be on track for the month. 

thanking you in advance

[amitchandak]

If you drag both dates in the matrix visual, on row and one on the column. It should give date hierarchy option. On one side choose date(Booking) another side choose Month year(Arrival).

And take count of the customer as measure

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[ninakarsa]

thanks for your reply. Once I get the data in that form I want to create the following measures:

cumulative customers: for example for 05.4.19 this will be 8

booking ratio: cumulative customers/ number of booking dates

eg for 05.04.19 this will be 8/5= 1.6

bookings until the end of the month: booking ratio * the number of remaining days left to make bookings + bookings already made

eg for 05.04.18 this will be 1.6 * (30-5)+8=48

	Apr
Booking Date	no customers	Cumulative customers	booking ratio	bookings until end of the month	target for the month
01/01/2019	4	4	4.00	120	50
02/01/2019	2	6	3.00	90	50
03/01/2019		6	2.00	60	50
04/01/2019		6	1.50	45	50
05/01/2019	2	8	1.60	48	50
06/01/2019	2	10	1.67	50	50
07/01/2019	5	15	2.14	64	50
08/01/2019		15	1.88	56	50
09/01/2019	4	19	2.11	63	50
10/01/2019	1	20	2.00	60	50

[edhans]

@ninakarsa I have a few questions about your request for measures:

1. What is your date format? Is 2/1/2020  January 2, 2020 or February 1, 2020? IOW M/D/Y or D/M/Y?
2. I don't understand your cumulative example. for 5.4.19 (may 4 or apr 5?) how are you getting 8? By the 4th/5th month, there are a lot more than 8 customers.