Assigning a Date to a Calculated Column or Measure then Displyaing using a Clustered Column Chart

[jonathan_nz]

I need help with creating a Clustered Column Chart. I am a Power Bi novice (couple of months under my belt).

The problem:

I am working with sales data that is generated over multiple months (its a rental car business).

For example, a booking will have a pickup date of say 30 January and a drop off date of say 5 February. I have used a calculated column to allocate the number of 'booking days' across the different months (i.e. 2 days in January and 5 days in February). This calculated column is then multiplied by the daily rate to get revenue per month. I have 12 calculated columns (or measures) for the 2017 calendar year (one for each month). I have tried to graph this data but the results are not satisfactory when you have 12 months of data (the columns get smaller and smaller).

What I need to do is somehow assign a date to each monthly sales total but am struggling to do this. I have tried to create a 'date' column using if statements but this does not seem to work as the sales data is over multiple time periods / columns and the if statement can only assign one date per row. I need to somehow 'fix' or create an 'absolute' sales amount per month and then assign a date to that fixed amount - but I can't work out how to do this.

Monthly Sales (note: no shared axis value - this needs to be in months)

This is what happens if I use an if statement to assign a date, it picks up February sales (dark green) where there is also a January sale (i.e. a booking across multiple months) and includes the result in January. This data should be in February (and combine with the other February data). You will note that there is now a shared axis though (which is want I am after) and visually the clustered column chart looks better.

Thanks in advance for any assistance you can provide. I am wasting way too much time on this!

If you want to look at my DAX formulas let me know. They are all pretty basic. 

Regards, Jonathan

[parry2k]

You explained well, but it will be nice to see some sample data to provide help.

[jonathan_nz]

Sure, I'm not sure how to upload an excel file, so here is a snapshot of the data I am working with:

pickup_date	dropoff_date	7 - Total Day Count	6 - Daily Sales	C - January 2017 Day Count	C - February 2017 Day Count	M - January 2017 Sales	M - February 2017 Sales
30/09/2016	6/02/2017	129	$42.90	31	6	$1,329.90	$257.40
6/01/2017	30/03/2017	83	$72.68	25	28	$1,817.00	$2,035.04
10/01/2017	27/03/2017	76	$42.14	21	28	$884.94	$1,179.92
3/11/2016	7/01/2017	65	$68.93	7		$482.51
7/11/2016	4/01/2017	58	$88.72	4		$354.88
18/11/2016	13/01/2017	56	$44.64	13		$580.32
8/02/2017	27/03/2017	47	$59.44		20		$1,188.73
10/12/2016	24/01/2017	45	$48.92	24		$1,174.08
20/01/2017	3/03/2017	42	$47.94	11	28	$527.31	$1,342.24

Dates are in NZ format, so the day and month are opposite to the US convention.

Cheers, Jonathan

[Anonymous]

Hi @jonathan_nz,

It is hard to use measure or calculate column to modify the display graphic, perhaps you can try to write a measure to calculate the next month amount based on current month.

Regards,

Xiaoxin Sheng