Forum Discussion

PBInonpro's avatar
PBInonpro
Icon for Helper III rankHelper III
3 years ago

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):

 

No RepliesBe the first to reply