Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@Anonymous,
Please check DAX in the attached PBIX file.
Regards,
Lydia
@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
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
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
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |