How to convert/show monthly margin from single row of data with a given start and end date?

[Anonymous]

I have the below data and want to create a table and graphs to show billings/margin made each day, month, quarter, year by Consultant but the data is in one row I have a mental block on how to translate it to get what I need. PLLLLEEASSE HELPP!

start_date	end_date	outcome	rate1_payment	rate1_invoice	days_per_week	Consultant 1 %	Consultant 2 %	Number of days	Total No of days working	Daily Margin	Con1 daily Margin	Cons2 daily margin	Cons 1 Total Margin of Contract	Cons 1 Total Margin to Date	Cons 2 Total Margin of Contract	Cons 2 Total Margin to Date
06/03/2017	15/04/2017	F3	1300	1857.14	5	0.9	0.1	40	40	557.14	501.426	55.714	£ 20,057.04	£ 19,555.61	£   2,228.56	£      2,172.85
11/04/2016	01/06/2017	F3	650	747.13	5	0.5	0.5	416	416	97.13	48.565	48.565	£ 20,203.04	£ 17,871.92	£ 20,203.04	£   17,871.92
11/04/2016	23/05/2016		650	747.5				42	0	97.5	0	0	£                -	£                -	£                -	£                  -
14/11/2016	23/01/2017	F2	650	812.5	5			70	70	162.5	0	0	£                -	£                -	£                -	£                  -
05/04/2017	24/04/2017	F3	650	866.67	5	1		19	19	216.67	216.67	0	£   4,116.73	£   1,950.03	£                -	£                  -
28/02/2017	03/03/2017	F3	600	750	3		100	3	1.8	150	0	9000	£                -	£                -	£ 16,200.00	£ 405,000.00

[Anonymous]

Hi @Anonymous,

Could you please post expected result in table? I am not quite sure which logic you use to calculate billings/margin made each day, month, quarter, year by Consultant.

Thanks,
Lydia Zhang

[Anonymous]

Taking a line in original table, I guess to get it so that when I visualise I can drill down from year, quarter, month, day for any given contract.

start_date	end_date	outcome	rate1_payment	rate1_invoice	days_per_week	Consultant 1 %	Consultant 2 %	Number of days	Daily Margin	Con1 daily Margin	Cons2 daily margin	Total No of days working
06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
To something like this;
Dates in Contract	start_date	end_date	outcome	rate1_payment	rate1_invoice	days_per_week	Consultant 1 %	Consultant 2 %	Number of days	Daily Margin	Con1 daily Margin	Cons2 daily margin	Total No of days working
06/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
07/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
08/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
09/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
10/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
11/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
12/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
13/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
14/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
15/03/2017	06/03/2017	15/04/2017	F3	1300	1857.14	4	0.9	0.1	92	150	135	15	73.6
continued to the end date….

[Anonymous · Accepted solution]

Hi @Anonymous,

In Query Editor of Power BI Desktop, add a custom column with the formula below.

{ Number.From([start_date])..Number.From([end_date]) }

Then expand the custom column and format its type to “Date”, for more details, please review the following screenshots. This way, you will get your expected result.

Thanks,
Lydia Zhang

[Greg_Deckler]

Take a look at this recent thread. Your data format looks similar in format and you probably need to do similar things to unpivot the data.

https://community.powerbi.com/t5/Desktop/M-Language-unpivot-issue/m-p/159158

[Anonymous]

Thank you so much for the response. I can see what you are getting at but can't seem to get my head around how I get the start and end dates unpivoted and showing every date inbetween the start & end repeating all the other information that I can then present/report over periods?