Calculating changes to Room sales and revenue

[iqbal]

Hello,

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.
Iqbal

This text will be blurred

Revenue.pbix (1.1 MB)

[Melissa]

Hi @iqbal,

Welcome to the Forum!
Big thanks for providing a sample PBIX file

@iqbal

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…

If you only want to show one of these measures at a time, you can incorporate something that works like a What if Parameter to allow for the interval change. Since you’re looking for a custom date interval, I would set that up manually. How to create a manual table is shown here secondary table logic.

.

@iqbal

the output is inaccurate as the measure is based on reading date where it should be based when reading date = revenue date

You can add a Boolean IsSameDate check in Power Query to your facttable, so the filtering requirement becomes a breeze.

.

@iqbal

Calculate rooms revenue at the beginning of the month

A StartOfMonth attribute in your Date dimension table, could be helpful but is not required because DAX has a built in STARTOFMONTH function.

.

@iqbal

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

In a recent topic @Nick_M covers a technique to do just that, you can find that here.

SamperiodlastYear Working Days

@Neville, I think if you used DATESBETWEEN (like @JBright mentioned) combined with using SAMEPERIODLASTYEAR and FIRSTDATE/LASTDATE you will get the output you are expecting, well hopefully Previous Working Days = /*Variables to define what the previous start and end dates are*/ VAR _PrevStart = SAMEPERIODLASTYEAR( FIRSTDATE( 'Date'[Date])) --Using the Current Filter context, goes back one year from the start VAR _PrevEnd = SAMEPERIODLASTYEAR( LASTDATE('Date'[Date])) --Usi…

.
BTW you have two Date tables in your model both connected to your fact table.
How do you intent to use those tables?? Many times I find that just one Date table will suffice.

Comparison of data using two date slicers

Hi @YogeshM, Please see if this article helps you to slove the issue.

.

I hope this is helpful in getting you started.
If you need further assistance, please post your questions and updated PBIX file. Thanks!

[EnterpriseDNA]

Hi @iqbal, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[iqbal]

Thanks for the response.I have updated Pbix. I was able to resolved majority of my questions with your feedback and a bit more research. However, I have not been able to figure out the dax measure for “changes past” tab. In this section, I am filtering the data when reading date = revenue date. What I am looking to do is to go back certain day intervals and pull rooms sold data to match the filter context. This table should look similar to the one i have completed in the “changes- future” tab. The main issue is trying remove the current filter context, in this case when reading date = revenue date, and open all dates and fetch the room sold data.Any help with writing the dax measure is greatly appreciated.

Revenue.pbix (1.2 MB)

[Melissa]

Hi @iqbal,

See if this pattern and measure branching technique solves it for you.

+3 days = 
CALCULATE(
   [Past 0d],
    DATEADD( 'Calendar'[Date], 3, DAY )
)

If not please provide an example with expected outcome, thank you.

[iqbal]

Melissa, thanks for the quick response. The issue is when we write the Dax measure this way, it works within the confine of the original logic, meaning when the reading date = revenue date & fetches rooms data that was actualized 3 days ago. As an example,this measure for actualized date of 2/29/2020 will bring the data for the actualized date of 2/26/2020. This room sold count data belongs to 2/26/2020 & not for 2/29/2020 if the measure is written in the current filter context.

What I want to bring is the room sold count for 2/29/2020 3 days ago, in other words, what was the room sold count for 2/29/2020 on 2/26/2020 - I am looking for changes in rooms sold 3 days ago to now. So any Dax measure must clear the current filter context applied in the table and access full data set in the fact sheet and then bring the rooms sold data taken on the 2/26 for 2/29. So this is my issue.

[Melissa]

@iqbal

in other words, what was the room sold count for 2/29/2020 on 2/26/2020

So what am I missing here?

In other words how can I identify rooms sold data on the 2/26 for 2/29 based on the data provided?
What would be the expected outcome? is that 256 ??

if so then try this:

+3 days v2 = 
CALCULATE( SUM( 'Sales_Revenue Fact sheet'[Rooms Sold]),
    FILTER( ALL( 'Sales_Revenue Fact sheet' ),
        'Sales_Revenue Fact sheet'[Reading Date] = MAX( 'Calendar'[Date] ) &&
        'Sales_Revenue Fact sheet'[Revenue Date] = DATEADD( 'Calendar'[Date], -3, DAY )
    )

I hope this is helpful.

[iqbal]

Thank you so much. I think we are close to the answer. Please see the attachment for the answer that I am looking for. Essentially, we will need to go back -3 days from actualized date in question, in this example, 2/29/2020. The latter date is essentially the reading date, then need to go back 3 days, which means, the reading date should be 2/26/2020 and then match the current revenue date which is 2/29/2020 and get the room sold number. For this example, the rooms sold number on the reading date 2/26 for 2/29 revenue date was 253.

[Melissa]

Alright. Thanks for that @iqbal, think this does the trick.

+3 days v3 = 
CALCULATE( SUM( 'Sales_Revenue Fact sheet'[Rooms Sold]),
    FILTER( ALL( 'Sales_Revenue Fact sheet' ),
        'Sales_Revenue Fact sheet'[Reading Date] = MAX( 'Calendar'[Date] ) &&
        'Sales_Revenue Fact sheet'[Revenue Date] = DATEADD( 'Calendar'[Date], 3, DAY )
    )
)

[iqbal]

I just attached an excel sheet to show the expected output of the report. I added column D for illustration purpose only but not expected in the output of the report.

PU test.xlsx (11.4 KB)

[iqbal]

I switched the formula a bit and it worked.

CALCULATE( SUM( ‘Sales_Revenue Fact sheet’[Rooms Sold]),
FILTER( ALL( ‘Sales_Revenue Fact sheet’ ),
‘Sales_Revenue Fact sheet’[Revenue Date] = MAX( ‘Calendar’[Date] ) &&
‘Sales_Revenue Fact sheet’[Reading Date] = DATEADD( ‘Calendar_2’[Date], -3, DAY )))

Thanks for your help.

[Melissa]

Your welcome @iqbal, here’s an alternative without the second date table.

CALCULATE(
    CALCULATE( SUM( 'Sales_Revenue Fact sheet'[Rooms Sold]),
        FILTER( ALL( 'Sales_Revenue Fact sheet' ),
            'Sales_Revenue Fact sheet'[Reading Date] = MAX( 'Calendar'[Date] ) &&
            'Sales_Revenue Fact sheet'[Revenue Date] = DATEADD( 'Calendar'[Date], 3, DAY ) 
        )
    ), DATEADD( 'Calendar'[Date], -3, DAY )
)

[iqbal]

Perfect. Thanks. The proposed one worked too. I am not sure why the previous one worked without an active relationship between revenue date and calendar.

[Melissa]

That’s because it fully calculates in context of the Reading Date…

Inside FILTER we check all Reading Dates equal to the Calendar Date in the table visual (= current context), FILTER then returns a table with all matching rows, next we add a second condition to FILTER where the Revenue Date is equal to the Calendar Date -3 days before we SUM Rooms Sold

I hope this is helpful

[EnterpriseDNA]

To the contributor of this post. Thank you for sharing your experiences around Power BI, please don’t hesitate to add more discussion or add value to wherever you think you possess the experience or knowledge that can help others in our Ecosystem Group. You can also help us in improving the Support forum further by answering the Enterprise DNA Forum User Experience Survey. We appreciate the initiative and your help in this group!

[EnterpriseDNA]

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up