Forum Discussion

SpiroswayGR's avatar
SpiroswayGR
Icon for Resolver III rankResolver 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!

     

SpiroswayGR's avatar
SpiroswayGR
Icon for Resolver III rankResolver III
6 years ago

Data set 

 

Anonymous

Anonymous 

 

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity 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!