cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdvD Frequent Visitor
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.

 

ProjectIDStart dateDurationRevenu 
2017009761-9-2019 00:004920000000
2018031081-1-2019 00:00121000000
771506392 020386
2016683171-10-2018 00:002420000000
2016681621-8-2020 00:00123000000
201702469 099999
201702465 020000
201702467 07777
1017200114-12-2017 00:0002000
201603721-11-2018 00:0015800000
1018215171-10-2018 00:00480
201700645 00
101720466 02555
20170161015-11-2018 00:0061250000
101720333 06176
101717654 060376
2017008421-1-2022 00:001215000000
2017009831-7-2018 00:0061000000
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: total project revenue distributed over time

Hi @AdvD,

 

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

Re: total project revenue distributed over time

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

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.
AdvD Frequent Visitor
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
Community Support Team

Re: total project revenue distributed over time

Hi @AdvD,

 

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.