cancel
Showing results for
Did you mean:
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
Community Support Team

## 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
Community Support Team

## 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

Community Support Team

## 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.