Difference Between Two Snapshots

[badkins2]

 Hello,

I have been researching in this community, but am new to DAX and have not been able to solve my problem. 

I have daily data coming into a SQL Server table showing a snapshot of future meeting room space rented with a column inserted showing a timestamp of day of import. I am trying to make a matrix that shows data for the most recent timestamp and then change in guests and revenue since the previous timestamp (in this case, one day prior). A sample of dummy data is below, followed by what I am targeting. This daily data has Arrival Dates all the way to a year in the future and numerous Room numbers.

My Sample File is here

Current

Target

I would previously accomplish this with SUMIFS formulas in Excel, but have been unable to recreate in DAX. I have fooled around with the DATESBETWEEN function to no avail after searching the forums. Any help or links to threads I may have missed is much appreciated.

Thanks.

[DataInsights]

@badkins2,

Try this solution.

1. Create a date table and mark it as a date table. You can use CALENDARAUTO, etc.

2. Create a relationship between the date table and data table on the Timestamp column:

3. Create measures:

Total Guests = SUM ( Table1[Guests] )

Total Revenue = SUM ( Table1[Revenue] )

One Day Change Guests = 
VAR vAmountYesterday =
    CALCULATE ( [Total Guests], DATEADD ( DimDate[Date], -1, DAY ) )
VAR vAmountToday = [Total Guests]
VAR vResult = vAmountToday - vAmountYesterday
RETURN
    vResult

One Day Change Revenue = 
VAR vAmountYesterday =
    CALCULATE ( [Total Revenue], DATEADD ( DimDate[Date], -1, DAY ) )
VAR vAmountToday = [Total Revenue]
VAR vResult = vAmountToday - vAmountYesterday
RETURN
    vResult

4. Create visuals: