Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |