Forum Discussion
Planning and Spending datasets. Relationship guide requested!
Please see attached (fake data). This is for a school program with a Fiscal Year of July - June (June is FY22 and July is FY23, etc).
There are two documents:
1) Plan - the plan to spend for the FY
2) Spending - accumulated month by month (same data in report for month prior)
The common ID between both is "Bus_Center" but there is no distint lookup table.
The 'plan' spreadsheet has July - Jun columns and a column for the 'budget' vs 'planned' which is the remaining.
Several questions:
1 - need to formule the budget and spent per business center (Bus_Center).
2 - need to fumulate the budget and spent per other entities (Project, Subproject, Location, Specialty_Code, etc).
3 - How to adjust a calendar table for FY starting Jul 1.
My assumptions.
I unpivot the month columns to have one column for 'month' and one column for 'month_budget'. 'Month_budget' column would be a dax formula to divide by 12 to get the total planned budget (what was planned total for July - Jun).
I create a distinct table for BUS_CENTER, if not all the elements I want to use.
For a stacked column, I would need to align the month names with numbers (Jul = 1, Aug = 2, .. Jun = 12)
If I am on the right track, I have several more questions.
(apparently I can't attached documents (csv and excel) ... so attached are screenshots)
(edit: I also catch myself making dax measures for each category of budget and think there can be one catch-all)
PLAN:
SPENDING (JUL):
SPENDING (AUG):
SPENDING (SEP):