Forum Discussion

badkins2's avatar
badkins2
Frequent Visitor
5 years ago

Difference Between Two Snapshots

 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.

 

 

 

 

 

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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: