Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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