Attributing Net Income to Relevant Months Based on Start and End Date

[Anonymous]

I have a start and an end date, and a net income for each 'type' below:

Type	Start	End	Net Income
A	15/01/2018	22/02/2018	100,000
B	20/01/2018	15/02/2018	120,000
C	03/02/2018	06/03/2018	200,000
D	15/02/2018	10/03/2018	60,000
E	16/02/2018	02/03/2018	50,000
F	21/02/2018	20/03/2018	140,000

What I would like to do is weight that net income by month based on the start and end date... In excel I would just create a matrix for the relevant months to get the sum of weighted income as below:

Type	Start	End	Net Income	January	February	March
A	15/01/2018	22/02/2018	100,000	44,737	55,263	0
B	20/01/2018	15/02/2018	120,000	55,385	64,615	0
C	03/02/2018	06/03/2018	200,000	0	167,742	32,258
D	15/02/2018	10/03/2018	60,000	0	36,522	23,478
E	16/02/2018	02/03/2018	50,000	0	46,429	3,571
F	21/02/2018	20/03/2018	140,000	0	41,481	98,519
				100,121	412,052	157,826

I would like to replicate this in Power BI through the table or matrix visualisation, is there a way for me to do this without having to create additional columns for each month in the data tables (as above)? I was hoping to use a calendar lookup table to generate weighted net income each month - all help is appreciated.

Please note, I need to weight the net income basis the days in each month, it cannot just be split evenly across months

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please check DAX in  the attached PBIX file.


Regards,
Lydia

[v-yuezhe-msft]

@Anonymous,

Take type A for example, what logic do you use to the following result in Jan and Feb?

44,737

55,263

Regards,
Lydia

[Anonymous]

Admittedly that was done in a rush and may be the wrong exact numbers but the logic is:

(Days in January / Total Duration) * Net Income

[v-yuezhe-msft]

@Anonymous,

For Jan, the value is 15/38*100000, for Feb, the value is 22/38*100000, right?

Regards,
Lydia

[Anonymous]

16/38 * 100,000 for Jan I think - please note I need to do this for a very large data set with hundreds of rows like the example I used

[Anonymous]

I should have said as well that the dates range over a 9 year period - what I'd like to be able to do is have a monthly breakdown over the entire period with net income attributed to each month