I am new to power BI. I am in the process implementing power BI across our organization. I have attached the Sales data and revenue PBIX file as I am struggling find ways to calculate the measurements for the business process.
The file contains rooms sales and revenue data at each reading date. This provides a snapshot of revenue at the hotel. The definition of reading is the the date that the sales and revenue data was generated. Reading date is aka recorded date.
Furthermore, the revenue date is the actual date of the sales and revenue at the hotel. The file contain past and future looking data.
I am looking to find answers to the following key business metrics.
a) Futures dates - Calculating changes (absolute value) to rooms and revenue from a given time in the past. For an example, if I want to see the change in room sales and revenue to 7 days ago to now, I would write the DAX measure - calculate(sum(sales_revenue fact sheet[rooms sold], dateadd(rolling calendar look up[date]),-7,day)). Once I obtained the rooms sold 7 days ago, i just do a basic subtraction to get the change. This measure works well, but I have to write 5 - 10 measures and separately subtraction measures to supplement to get the changes in rooms sold for multiple intervals. Example - 1, - 3 ,-7,-14, -21, -28 etc…Not sure this is the optimum way of doing this.
b) Past dates - The same calculation works for past dates but the output is inaccurate as the measure is based on reading date where it should be based when reading date = revenue date, then should go back 7 days and fetch rooms sold 7 days ago. I tried different options to get the output but the dax measure seems complex. I set up two inactive relationship to the date table and also tried setting up two calendar tables with two separate active connections. Both options didn’t work.
c) Calculate rooms revenue at the beginning of the month - want to write a dax measure to compute room revenues and rooms revenue at the start of the month. I want to be able to generate results for past and future months.
Filter - start of the month
Month& Year | Rooms Sold | Rooms Revenue
Jan 2020 | xxxx | xxxx
Feb 2020 | xxxx | xxxx
Mar 2020 | xxxx | xxxxx
Apr 2020 | xxxx | xxxxx
May 2020 | xxxx |
June 2020 | xxxx | xxxxx
July 2020 | xxxx | xxxxx
d) Last one. I want to be able to go back relative to the current date and fetch rooms sold and rooms revenue. This is similar same time last year
If the current date is 6/9/2020, to go back to relative reading date and fetch rooms sold and rooms revenue.
| This year | Same time Last year |
Month | Rooms Sold | Rooms Revenue | Month | Rooms Sold | Rooms Revenue |
June 2020 | xxxx | xxxxx | June 2019 | xxxx | xxxxx |
July 2020 | xxxx | xxxx | July 2019 | xxxx | xxxxx |
Aug 2020 | xxxx | xxxx | Aug 2019 | xxxx | xxxxx |
Sep 2020 | xxxx | xxxx | Sep 2019 | xxxx | xxxxx |
Appreciate any help.
Thanks in advance.
This text will be blurred
Revenue.pbix (1.1 MB)