SnapShot Table with Metrics

[SpiroswayGR]

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

[Anonymous]

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]

Data set 

@Anonymous

@Anonymous 

[v-kelly-msft · Accepted solution]

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]

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]

@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]

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.