cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JBPowerBI Frequent Visitor
Frequent Visitor

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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@JBPowerBI,

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
Moderator v-yuezhe-msft
Moderator

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

@JBPowerBI,

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.
JBPowerBI Frequent Visitor
Frequent Visitor

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

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

Moderator v-yuezhe-msft
Moderator

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

@JBPowerBI,

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.
JBPowerBI Frequent Visitor
Frequent Visitor

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

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

JBPowerBI Frequent Visitor
Frequent Visitor

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

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

Moderator v-yuezhe-msft
Moderator

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

@JBPowerBI,

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 134 members 1,753 guests
Please welcome our newest community members: