Forum Discussion
Multiply weekly Budget based on Filtered Month Selected
HI
I have several tables for monitoring budgets against spend by month.
Current layout allows user to filter report using several slicers in order to review 100's of various budgets.
All works fine, however, I now need the end user to be able to filter by month so they can see the monthly spend versus the monthly budget, currently only shows by Yearly Budget
Slicer: Calendar Month
1. Measure 1 = "Budget per Week" i.e Total Yearly Budget divided by 52
2. Measure 2 = "No. Weeks" i.e Weeks per Calendar Month
Result Given: Just gives Total budget, but when month is selected the values go blank, I thought I needed to put the relationship between budget & calendar tables to both, however, if I do this, it then in turn breaks other visuals by filtering it all to current month, which is not what I want......only need the above new measure to filter.
Result needed: I am missing a line for the filtering of the Calendar Month slicer, by one or multi selected months, however I have tried several options by using Selectedvalue, Calculate, Filter etc. but the end result is not populating
Any ideas what I'm doing wrong I would really appreciate it.
Thanks
The fact that you're using the KPI visual makes a BIG difference. I'm not sure how it will behave with a non-date trend. Do you have a proper Date table rather than just the Months table? If you get a Date table, you should be able to create a generic budget measure:
Budget = SUM(Budget[Value])and monthly spend measure:
Spend = SUM(SpendTable[SpendValue])
Ensure both the Budget and the SpendTable are connected to the Date table using a Date or DateKey column (even if budgets are monthly, you'll need to pick a day of the month - I use the 1st of the month) with a one to many relationship for both. Then put Calendar Month in the trend and the KPI visual will do the rest of the filtering/work for you based on the latest calendar date selected in your filter/slicer/report.
Does that make sense?
3 Replies
LittleAnners_08 This looks like a context error. What is the formula for your two measures please that you're referencing and what fields do you have in the visuals where you want this to work?
Thanks for coming back to me:
1. Measure 1 = "Budget per Week" The formula for this one is just a simple division:
Budget per Week = Divide(sum('Job Budgets'[ForecastYearBudget]),52)
2. Measure 2 = "No. Weeks" This is a simple sum measure driven by a calculated column that counts the number of weeks based on our working month:
Calculated column is: MonthWeeks = (Months[LastWorkDayDate]-Months[FirstWorkDayDate])/7+1
Which then has Measure 2:
No. Weeks = Sum(MonthWeeks)
The visual is a KPI gauge that shows :
- Indicator = Current Spend
- Trend = Team
- Target Goals = Monthly Budget (ie: formula with the Issue)
Thank you
The fact that you're using the KPI visual makes a BIG difference. I'm not sure how it will behave with a non-date trend. Do you have a proper Date table rather than just the Months table? If you get a Date table, you should be able to create a generic budget measure:
Budget = SUM(Budget[Value])and monthly spend measure:
Spend = SUM(SpendTable[SpendValue])
Ensure both the Budget and the SpendTable are connected to the Date table using a Date or DateKey column (even if budgets are monthly, you'll need to pick a day of the month - I use the 1st of the month) with a one to many relationship for both. Then put Calendar Month in the trend and the KPI visual will do the rest of the filtering/work for you based on the latest calendar date selected in your filter/slicer/report.
Does that make sense?
