I’m working on a project and I am a bit stuck on working out a ‘capacity’ type measure that aggregates over a period of time (based on context)
So its your usual orders / stores / dates type scenario, and I have a table for each (orders being my fact table here)
My ‘Stores’ dimension is a bit complex because I have some that are open on weekend days, and some that are not.
So for each Store ID, I have a daily Capacity value, and a true/false column for whether it operates on Saturday and Sunday
What I want to calculate is the total capacity for each store over a given date range, and be able to drill up/down to a daily or weekly level, with it calculating the correct value for a week period whether the particular store is operating on the weekend or not.
To start I have a measure that counts the number of dates in the current context
Number of Dates = COUNTROWS(Dates)
Then I’m working out a basic capacity like this:
Base Capacity By Date = VAR DatesSelected = [Number of Dates] RETURN SUMX ( 'Store Capacity', 'Store Capacity'[Capacity] * DatesSelected )
Which is okay, but not accurate enough. I tried adding another column to my Store dimension, giving the number of days per week, but I don’t think using VALUES on this column would work, i.e
VAR DatesSelected = [Number of Dates] VAR OperatingDays = VALUES ( 'Store Capacity'[Operating Days] ) VAR OpenDates = DatesSelected - ( DatesSelected - OperatingDays )
Any ideas? Happy to provide more info if needed