Calculate data based on yesterday's figures or weekly figures.

[Anonymous]

Hi,

I have this table of data:

The row dates are reservation dates for a hotel, and the colum shows when those reservations will stay in the future.

I need to calculate how many reservations were made yesterday, during the last 7 days and last 14 days.

I tried to use

CALCULATE ( [Roomnights], DATEADD(Reservation dates[date], -1,DAY))

but it doesn't work, it gives me a weird number that doesn't make any sense (2,783).

So results for this example should be:

Pu1 (sum of reservations made yesterday) = 43

Pu7 (sum of reservations made in the last 7 days) = 1,686  

Pu14 (sum of reservations made in the last 14 days) = 2,708 (as the example only has 12 days)

What am I doing wrong? 

Thanks for your help! 

[v-yiruan-msft]

Hi @Anonymous ,

Could you please share some sample data in table Roomnights & Reservation dates (exclude sensitive data)? Is there any relationship created between these two tables? Did you create date dimension table?

On the other hand, you can refer the content in the following links to get it.

DAX – PREVIOUSDAY Function

Calculate Transactions In Last N Days in Power BI Using DAX

Best Regards

[Anonymous]

By the way, using PREVIOUSDAY function doesn't provide the right figure either:

CALCULATE(Roomnights[Roomnights], PREVIOUSDAY('Reservation dates'[Date])) gives me 118 instead of 43