Forum Discussion

SpiroswayGR's avatar
SpiroswayGR
Resolver III
6 years ago
Solved

SnapShot Table with Metrics

Dear Community,   FIrst of all, we have Hotel Data with reservations and we would like to create a SnapShot table with some metrics like total revenue , Roomights etc.   Secondly,the problem is t...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi SpiroswayGR ，

     

    Create a table using an expression as below:

     

    Date = CALENDAR(MIN('Table'[System Date]),MAX('Table'[System Date]))

     

    Then use a dax expression:

     

    Measure = CALCULATE(SUM('Table'[Pax]),FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])),USERELATIONSHIP('Date'[Date],'Table'[System Date]))
-CALCULATE(SUM('Table'[Pax]),FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])),USERELATIONSHIP('Date'[Date],'Table'[Cancelation Date]),'Table'[Cancelation Date]<>BLANK())

     

    Finally,you will see :

     

     

    For the related .pbix file,pls click here.

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

1) Change the metric join to outer

2)In the grid Data -> VLDB properties -> Query Optimization -> SQL Global optimization -> Level 2 or Level3 ( default will be level 4).

3) In the grid Data -> VLDB Properties -> Query Optimization ->Joins -> Downward Outer Join Option-> Do not downward outer join for a database that supports full outer join.

 

  • SpiroswayGR's avatar
    SpiroswayGR
    Resolver III
    6 years ago

    Data set 

     

    Anonymous

    Anonymous 

     

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      6 years ago

      Hi SpiroswayGR ，

       

      Create a table using an expression as below:

       

      Date = CALENDAR(MIN('Table'[System Date]),MAX('Table'[System Date]))

       

      Then use a dax expression:

       

      Measure = CALCULATE(SUM('Table'[Pax]),FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])),USERELATIONSHIP('Date'[Date],'Table'[System Date]))
-CALCULATE(SUM('Table'[Pax]),FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])),USERELATIONSHIP('Date'[Date],'Table'[Cancelation Date]),'Table'[Cancelation Date]<>BLANK())

       

      Finally,you will see :

       

       

      For the related .pbix file,pls click here.

       

      Best Regards,
      Kelly
      Did I answer your question? Mark my post as a solution!