cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

total project revenue distributed over time

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
1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

Re: total project revenue distributed over time

Two concern:

1. How to deal with those records where date is null or duration is 0?
2. Why the total revenue for "1-01-2020" is 408k without adding up 83k?

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super Contributor

Re: total project revenue distributed over time

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

Re: total project revenue distributed over time

@v-yulgu-msft

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

Super Contributor

Re: total project revenue distributed over time

Two concern:

1. How to deal with those records where date is null or duration is 0?
2. Why the total revenue for "1-01-2020" is 408k without adding up 83k?

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Get your latest community news and announcements.

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 80 members 1,328 guests
Recent signins: