Compare One Sale Versus The Last Sale (No Time Intelligence)

DAX DAX Calculations
1

Hi Everyone

First of all I would like to thank you for all the knowledge that you kindly offered to us in the Learning Summit!
Congrats !!
I can imagine how much work you had in order to make it possible. Keep going…thanks.

I was watching the video “Compare One Sale Versus The Last Sale (No Time Intelligence)” and as I am working on a Snapshot Model - specifically for the hotel industry - I guess you can give me a help hand.

Basically the Snapshot Table contains the following collumns:

Snapshot Date
CheckinDate
Hotel
Segment
Room Revenue on Hand (On the Books)
Room Nights on Hand (On the Books)

My dimensions are: Date - Hotel - Segment

So every day I record what the hotel has for the past and future dates. In fact for the past dates there is no changes. Differently for the futures dates, where the Revenue and Bookings changes day by day what we call Daily Pickup.

From this video I could find a solution to calculate the Daily Pickup from yesterday to today.

Besides this, my goal is to calculate, for example, the average daily pickup for the last 3, 7, 14, 21, n days.

[Snapshot Table]

https://1drv.ms/x/s!AoOum-xIZ0T-mNQPKjP3OeRouicH1g

Let me know if you can help me with this solution.

By the way, Snapshots, it would be an idea for you to produce some videos about this subject…

Best Regards
José Ricardo

2

There’s actually quite a bit to this one. The solution is a little bit different to the video you mentioned here.

I will have to spend some time testing this.

Will come back when I have something.

Can you add the actual model in Power BI, that would be easier seeing the whole picture.

Thanks

3

Bookings Snapshot.pbix (346.5 KB)

4

Ok I understand it a bit better now. As I thought there’s a bit to this one.

You actually need to solve this in the model also. See below I have created an inactive relationship between the Date and the Snapshot date.

image
image.png591×505 9.68 KB

This is important because you can get the correct filters working by using the USERELATIONSHIP function in DAX

Revenue by Snapshot Date = 
CALCULATE( [Total Revenue],
    USERELATIONSHIP( Dates[Date], Snapshot[Snapshot Date] ))

image

Then you can use time intelligence functions to work out the difference or pickup.

See one example below for 3 days difference

3 Day Revenue Pickup = 
[Revenue by Snapshot Date] - CALCULATE( [Revenue by Snapshot Date], DATEADD( Dates[Date], -3, DAY ) )

Hopefully this makes sense.

Give this idea a go and let me know how you get one.