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 that each metric has one result (value) for each day. We need to save all of these values for each day on a table with each row = system date (01/01/2020 , 02/01/2020 , 03/01/2020) and then metrics per day.

 

How is that possible either with powerBI or any other way ? 

 

Thank you in advance for your help!!

 

 

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!

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    SImple drag date column in matrix visualization.

    And create measure as per your requirements like total revenue , total reservations.

    Add drag them in values section.

    Final snapshot will be like Total reservations & total revenue per date.

     

    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.

     

    • SpiroswayGR's avatar
      SpiroswayGR
      Resolver III
      6 years ago

      Anonymous  This is not working for me cause i want snapshot information ...

      For example : On 05/03 System date , i want total revenue from 01/01 to 05/03 , On System Date 07/03 , i want total revenue from 01/101 to 07/03  etc...

       

      So all these values per different system saved in a table and visualize them. (It should be like a cumulative chart but it's not cumulative in reality , its a snapshot image for each date)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Yes you can create measure as per your requirement.

         

        You can do cummulative calculations in power BI.

         

        Please share sample date and expected output.

        I will provide you exact dax.

         

        Thanks & regards,
        Pravin Wattamwar
        www.linkedin.com/in/pravin-p-wattamwar

        If I resolve your problem Mark it as a solution and give kudos.

         

  • 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.

     

      • 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!