Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
@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!)
Solved! Go to Solution.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
I actually did just that (I tried to post it, but it didn't go through... sorry)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
98 | |
76 | |
66 | |
59 |
User | Count |
---|---|
142 | |
106 | |
103 | |
85 | |
70 |