Forum Discussion
2 date ranges for data
Sure, that is my biggest downfall when asking for help...I get asked to elaborate a lot. So I am summing guest cards and I am showing by day of the week but I have divide the sum by the business hours that we were open so that days that when we are open for fewer hours I can see the sum of guest normalized. I was doing the comparison last year to this year and this is what my calcation looked like. GC Calendar / Average of Business_Hours =
DIVIDE( SUM('Property'[GC Last Year]), AVERAGE 'Property'[Business_Hours])) GC Last Year being a measure I created that looked at the year of the created date and only counted the guest cards if they met that criteria. I tried to replace the GC Last Year piece with the GC Calendar piece and I got an error.
If "GC Last Year" is a measure, you cannot SUM it, you only need to reference it. My guess is that you are just making confusion between measures and columns.
If it is a measure, you should call it this way:
DIVIDE(
[GC Last Year],
AVERAGE 'Property'[Business_Hours])
)Then, you can replace it with any other measure.
Moreover, I am not 100% sure, but that AVERAGE looks wrong to me... why do you AVERAGE hours? The calculation might not be accurate. if not wrong.
I am averging business hours because each location is open the same amount of hours for each day of the week Monday through Friday is 8 hours, Saturday is 7 hours and Sunday is 4 hours, by dividing the total number of guest cards by the number of hours we were open it normalizes the counts of guest cards so that Sunday and Saturday are more in line with the rest of the week even though they were open for fewer hours. Your suggestion on removing the sum worked! Thank you so much!
Anonymous.
Glad to hear the issue is solved. Please mark appropriate replies as solutions, that way, other community members would easily find the answer when they get same issues.
Regards,
Lydia