Forum Discussion
Measure affected by slicer choice
Hello,
I've been trying to figure out how to create a measure that is calculated based on slicer input.
This is my data:
Table 1: Budget ours per group for the year 2019
Budget hours | 2019 |
Group A | 220 |
Group B | 150 |
Group C | 180 |
Table 2:
Actual hours per group per day/month/year. This data can occur on any date.
Name | Group | Date | Number of hours |
Dave | Group A | 01-13-19 | 8 |
Roy | Group A | 01-15-19 | 8 |
Jamy | Group C | 02-20-19 | 8 |
Jenny | Group B | 02-20-19 | 6 |
Mary | Group A | 02-20-19 | 5 |
What I’m trying to accomplish:
I've made a slicer for the user to select a specific month or a specific period they want to see. For example:
Month 1, or, Month 1 up to and including Month 3, or, Month 3 up to and including Month 9.
This slicer then filters the actual hours to the selected period. This is working great.
My problem
The budget hours are only available per year, while my slicers should give the flexibility to show this data per month, or per week eventually.
My plan was to create a measure that calculates the budget hours for the selected period, since the budget hours are only available per year and not per month or per week.
So of someone used the slicer to select month 3 up to and including month 9, that means they selected a period of 6 months in total and thus I want to show 6/12 of the budget hours. For example Group A: 220 / 12 months * 6 months) Where 6 months is the slicer input that varies.
If someone used the slicer to select month 4 up to and including month 7 it would have to calculate: Group A: 220 / 12 months * 4 months.
I don't have much experience in DAX formulas yet, so any help would be appreciated! Many thanks in advance.
Best regards,
Dennis
5 Replies
Hi,
Can you try this...
Define 3 measures
Measure 1: StartDate = MIN(Actual[Date])
Measure 2: EndDate = MAX(Actual[Date])
Measure 3: NoOfDays = EndDate - StartDate
This will give you the number of days in the range user has selected in the measure " NoOfDays". You can then divide the Budget Hours by 365 and multiply by NoOfDays to get the desired result.
Note: Actually you don't need to create 3 measures. Instead, you can combine the formulae into a single one in your final calculation itself. I have shown the 3 measures for your understanding only. Further, you can modify the function to get the number of months in the user selection by using the date functions. But this will work irrespective of the user's choice of day, week, month or quarter.
v-juanli-msftCommunity Support
Hi DennisN
1.
Create a new table
date table = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"Month",MONTH([Date]))
Add "year", "month" in the slicers
2.create relationships
3. create measures in Sheet 4
Measure_number_of_months = DATEDIFF(MIN('date table'[Date]),MAX('date table'[Date]),MONTH)+1 Measure_budget = SUM(Sheet3[2019]) Measure_final = 12/[Measure_number_of_months]*[Measure_budget]Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Many thanks for the help! I will try the suggestions this week and get back to you if I managed to solve my issue.
Thanks again!- v-juanli-msft
Hi DennisN
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
Best Regards
Maggie
