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
Anonymous
Not applicable

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

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

 

TypeStartEndNet Income
A15/01/201822/02/2018100,000
B20/01/201815/02/2018120,000
C03/02/201806/03/2018200,000
D15/02/201810/03/201860,000
E16/02/201802/03/201850,000
F21/02/201820/03/2018140,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:

 

TypeStartEndNet IncomeJanuaryFebruaryMarch
A15/01/201822/02/2018100,00044,73755,2630
B20/01/201815/02/2018120,00055,38564,6150
C03/02/201806/03/2018200,0000167,74232,258
D15/02/201810/03/201860,000036,52223,478
E16/02/201802/03/201850,000046,4293,571
F21/02/201820/03/2018140,000041,48198,519
    100,121412,052157,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

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@Anonymous,

Please check DAX in  the attached PBIX file.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

@Anonymous,

Please check DAX in  the attached PBIX file.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

@Anonymous,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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

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.