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.
Hallo,
The following problem.
The company i work for wants to know how much work there is for the upcoming months/years.
Based on leads we know when a project starts, the duration and how much revenue it wil give in total.
What i want to make is a visual that displays the total revenue spread over the time period of the projects.
Looking forward the posible solutions.
ProjectID | Start date | Duration | Revenu |
201700976 | 1-9-2019 00:00 | 49 | 20000000 |
201803108 | 1-1-2019 00:00 | 12 | 1000000 |
771506392 | 0 | 20386 | |
201668317 | 1-10-2018 00:00 | 24 | 20000000 |
201668162 | 1-8-2020 00:00 | 12 | 3000000 |
201702469 | 0 | 99999 | |
201702465 | 0 | 20000 | |
201702467 | 0 | 7777 | |
101720011 | 4-12-2017 00:00 | 0 | 2000 |
20160372 | 1-11-2018 00:00 | 15 | 800000 |
101821517 | 1-10-2018 00:00 | 48 | 0 |
201700645 | 0 | 0 | |
101720466 | 0 | 2555 | |
201701610 | 15-11-2018 00:00 | 6 | 1250000 |
101720333 | 0 | 6176 | |
101717654 | 0 | 60376 | |
201700842 | 1-1-2022 00:00 | 12 | 15000000 |
201700983 | 1-7-2018 00:00 | 6 | 1000000 |
Solved! Go to Solution.
Hi @AdvD,
Two concern:
Based on above sample data, I created a calendar table:
CalendarTable = FILTER(CALENDAR(DATE(2017,12,1),DATE(2022,1,1)),DAY([Date])=1)
Suppose the original data table is called 'Tab_2', then, please refer to below formulas to new calculated tables:
Tab_3 = FILTER ( CROSSJOIN ( SELECTCOLUMNS ( FILTER ( Tab_2, Tab_2[Start date] <> BLANK () ), "Start Date", Tab_2[Start date], "Revenue", IF ( Tab_2[Duration] <> 0, Tab_2[Revenu ] / Tab_2[Duration], Tab_2[Revenu ] ) ), CalendarTable ), ( YEAR ( [Start Date] ) = YEAR ( [Date] ) && MONTH ( [Start Date] ) <= MONTH ( [Date] ) ) || ( YEAR ( [Start Date] ) + 1 = YEAR ( [Date] ) && MONTH ( [Start Date] ) > MONTH ( [Date] ) ) ) Tab_4 = SUMMARIZE(Tab_3,Tab_3[Date],"Revenue",SUM(Tab_3[Revenue]))
Best regards,
Yuliana Gu
Hi @AdvD,
If you want to get the total revenue across the whole dataset, you could try this measure:
Total revenue = CALCULATE ( Table[Revenue], ALL ( Table ) )
Regards,
Yuliana Gu
Thank you!,
However this is not completely what i was looking for.
What i am looking for is some way of building in time dimension.
I want to sum al projects and show their total revenue spread over the duration of the project.
So if we take in account only the first two project this would be:
Project: 201700976 20.000.000/49 which is 408k a month start date 1-09-2019.
Project: 201803108 1.000.000/12 which is 83k a month start date 1-01-2019.
(mind the european notation)
So
Date | Revenue |
1-01-2019 | 83k |
1-02-2019 | 83k |
‘’’’ | ‘’” |
1-08-2019 | 83k |
1-09-2019 | 83k + 408k = 491k |
1-10-2019 | 491k |
1-11-2019 | 491k |
1-12-2019 | 491k |
1-01-2020 | 408k |
Hi @AdvD,
Two concern:
Based on above sample data, I created a calendar table:
CalendarTable = FILTER(CALENDAR(DATE(2017,12,1),DATE(2022,1,1)),DAY([Date])=1)
Suppose the original data table is called 'Tab_2', then, please refer to below formulas to new calculated tables:
Tab_3 = FILTER ( CROSSJOIN ( SELECTCOLUMNS ( FILTER ( Tab_2, Tab_2[Start date] <> BLANK () ), "Start Date", Tab_2[Start date], "Revenue", IF ( Tab_2[Duration] <> 0, Tab_2[Revenu ] / Tab_2[Duration], Tab_2[Revenu ] ) ), CalendarTable ), ( YEAR ( [Start Date] ) = YEAR ( [Date] ) && MONTH ( [Start Date] ) <= MONTH ( [Date] ) ) || ( YEAR ( [Start Date] ) + 1 = YEAR ( [Date] ) && MONTH ( [Start Date] ) > MONTH ( [Date] ) ) ) Tab_4 = SUMMARIZE(Tab_3,Tab_3[Date],"Revenue",SUM(Tab_3[Revenue]))
Best regards,
Yuliana Gu
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |