Forum Discussion
Allocating Revenue by Month
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!)
Anonymous,
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.
2 Replies
- DataInsights
Super User
Anonymous,
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.
- AnonymousNot applicable
I actually did just that (I tried to post it, but it didn't go through... sorry)