I am in a fix and I know there is a solution somewhere for this. I have a data set in which I need to multiply the total capacity to the monthly days number and secondly i need to multiply the total capacity of the unit to DOW e.g. 4 sundays, 5 mondays , 4 wednesday of one month or more. I need this to be dynamic.
My current formula goes like this.
---- [Hotel Occupied Rooms] / (30*[No of rooms])
30 = is the number of days in that month it can be 31 the next month and 30 the month later
But what now if I want to apply the same formula for '' days of week'' I possibly cannot break down every single day and count how many times it appears on the calendar and even if i do how do I build a formula which can be dynamic without me doing the manual work.
I already tried using
[Hotel Occupied Rooms]/(Monthly(date)*[No of rooms]) for month count of 31 or 30 days dynamically or
[Hotel Occupied Rooms]/( day(date) *[No of rooms]) for tableau to count dynamically there are 4 sundays or 5 tuesdays
but doesn't help.