Allocating Revenue Month over Month based on Year 1, 2 and 3 projections

[enswitzer]

I have a table that looks like this:

*copy and pasted here for ease of use

Project IDIdRevenue - Year 1Revenue - Year 2Revenue - Year 3DP-F Date

10	null	null	null	12/19/2021
11	null	null	null	7/27/2021
14	3,723,000.00	7,690,000.00	12,644,000.00	6/8/2021
13	null	null	null	12/30/2020
12	null	null	null	2/17/2021
15	null	null	null	3/15/2021
16	null	null	null	1/11/2020
26	9,800,000.00	10,800,000.00	11,800,000.00	10/29/2020
47	60,000.00	100,000.00	120,000.00	9/15/2021
28	1,919,090.00	3,826,306.00	5,717,645.00	12/1/2020
48	15,050,675.00	16,255,729.00	17,556,187.00	9/29/2020
40	200,000.00	300,000.00	450,000.00	11/15/2020
31	null	null	null	10/15/2020
41	922,957.00	1,095,185.00	1,303,462.00	10/25/2020
46	272,000.00	408,000.00	448,800.00	12/15/2020
54	1,286.00	2,248.00	4,012.00	10/30/2020
55	2,602.00	4,637.00	6,877.00	10/30/2020
62	null	null	null	9/22/2020
64	null	null	null	5/31/2021
76	null	null	null	10/22/2020
77	null	null	null	5/1/2021
96	589.00	729.00	865.00	11/27/2020
100	835,000.00	876,750.00	964,430.00	3/31/2022
105	693,380.00	700,528.50	729,121.50	11/30/2020
154	743,230.00	891,870.00	981,060.00	12/15/2020

I would like to be able to output a table that shows monthly revenue based on the DP-F date in the last column. So if the project ends on 6/8/2021, as of July 1, 2021 we would start calculating revenue based on the Year 1 Revenue Number. The output would look something like this:

Project ID	7/21	8/21	9/21	10/21	...	8/22	9/22	10/22	11/22
14	.31M	.31M	.31M	.31M	...	.64M	.64M	.64M	.64
48	0	0	0	1.25	...	1.25	1.25	1.35	1.35

I have NO idea where to start on this - any suggestions?

[DataInsights · Accepted solution]

@enswitzer,

Try this solution:

1. Create a calculated table. The data table is ProjectRevenue.

AllocatedProjectRevenue = 
GENERATE (
    ProjectRevenue,
    VAR vProjectID = ProjectRevenue[Project ID]
    VAR vProjectEndDate = ProjectRevenue[DP-F Date]
    VAR vAllocationStartDateYear1 =
        EOMONTH ( vProjectEndDate, 1 )
    VAR vAllocationEndDateYear1 =
        EOMONTH ( vAllocationStartDateYear1, 11 )
    VAR vAllocationStartDateYear2 =
        EOMONTH ( vProjectEndDate, 13 )
    VAR vAllocationEndDateYear2 =
        EOMONTH ( vAllocationStartDateYear1, 23 )
    VAR vAllocationStartDateYear3 =
        EOMONTH ( vProjectEndDate, 25 )
    VAR vAllocationEndDateYear3 =
        EOMONTH ( vAllocationStartDateYear1, 35 )
    VAR vCalendar =
        CALENDAR ( vAllocationStartDateYear1, vAllocationEndDateYear3 )
    VAR vCalendarEOM =
        FILTER ( vCalendar, [Date] = EOMONTH ( [Date], 0 ) )
    VAR vRevenueYear1 = ProjectRevenue[Revenue - Year 1]
    VAR vRevenueYear2 = ProjectRevenue[Revenue - Year 2]
    VAR vRevenueYear3 = ProjectRevenue[Revenue - Year 3]
    VAR vResult =
        ADDCOLUMNS (
            vCalendarEOM,
            "Amount",
                SWITCH (
                    TRUE (),
                    [Date] >= vAllocationStartDateYear1
                        && [Date] <= vAllocationEndDateYear1, vRevenueYear1 / 12,
                    [Date] >= vAllocationStartDateYear2
                        && [Date] <= vAllocationEndDateYear2, vRevenueYear2 / 12,
                    [Date] >= vAllocationStartDateYear3
                        && [Date] <= vAllocationEndDateYear3, vRevenueYear3 / 12
                )
        )
    RETURN
        vResult
)

2. In the table AllocatedProjectRevenue, set the Date column data type to Date, and format as Mm/yy.

3. Create a matrix visual based on the table AllocatedProjectRevenue.

[enswitzer]

Thank you this worked PERFECT!!!

[v-lionel-msft]

Hi @enswitzer ,

What is the calculation logic of the values in the matrix? Please explain in detail.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.