Forum Discussion
Multiply weekly Budget based on Filtered Month Selected
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?
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?