Allocating Revenue by Month

[enswitzer]

@DataInsights was CRAZY helpful and created this calculated table that allowed me to allocate revenue by month. I'm still learning and having a little trouble following it. Is there an easy way for me to update this so that it allocates years into Fiscal Years (July through June) instead of calendar years?

AllocatedProjectRevenue = 
GENERATE (
    'PP - Project Status: Revenue',
    VAR vProjectID = 'PP - Project Status: Revenue'[Project IDId]
    VAR vProjectEndDate = 'PP - Project Status: Revenue'[DP-F]
    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 = 'PP - Project Status: Revenue'[Revenue - Year 1]
    VAR vRevenueYear2 = 'PP - Project Status: Revenue'[Revenue - Year 2]
    VAR vRevenueYear3 = 'PP - Project Status: Revenue'[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
)

To Clarify - I think I'm the one that made the year part happen, and probably not eloquently:

This Year Additional Revenue = SUMX(filter(AllocatedProjectRevenue, AllocatedProjectRevenue[Year]=year(today())),AllocatedProjectRevenue[Amount])

2 Year Out Revenue = SUMX(filter(AllocatedProjectRevenue, AllocatedProjectRevenue[Year]=(year(today())+2)),AllocatedProjectRevenue[Amount])

3 Year Out Revenue = SUMX(filter(AllocatedProjectRevenue, AllocatedProjectRevenue[Year]=(year(today())+3)),AllocatedProjectRevenue[Amount])

So if there are any suggestions to make this by fiscal year instead - it would be much appreciated! (and to just clean up my code, I'm definitely trying to learn!)

[DataInsights · Accepted solution]

@enswitzer,

Glad you found the calculated table helpful. I believe the simplest approach would be to add the columns Fiscal Year and Fiscal Month to your Date table. Then, create a relationship between the calculated table and the Date table (using the Date column in each table). Now you can create a visual and filter/slice on Fiscal Year/Month.

If you could provide a mockup of the desired result for a particular project, I'll take a look.

[enswitzer]

I actually did just that (I tried to post it, but it didn't go through... sorry)