total project revenue distributed over time

[AdvD]

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

[v-yulgu-msft]

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

[AdvD]

@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

[v-yulgu-msft · Accepted solution]

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