Combine sum of two tables depending on dates

[Anonymous]

Hi,

I have this situation where I need to sum from one table for a specific date range and from another table with another date range in the same measure, is that possible?

The example:

This is a table from a hotel. 

Filters:  range of this year dates (stay dates calendar)

Calculations: 

 - Daily revenue for this year. Dates selection = stay dates calendar (done)

- Daily revenue for last year (need help) 

Tables involved:

- Stay dates calendar

- Snapshot dates calendar 

- Room revenue (consolidated) > to work with stay dates calendar only

- Room revenue (forecast) > to work with snapshot dates calendar only

The calculation I need to have is the sum of both red marked data:  from table 1 (Room Rev: consolidated revenue) the sum of all period until yesterday last year and from table 2 the sum from today last year until the end of the specified period. 

I assume I need a variable that would calculate that break point where I don't need to sum from the consolidated revenue (Room Rev. column in the pic) and start summing from the forecast revenue (uNetRev column in the pic). But I don't know how to create the whole formula in DAX in order to sum from both tables (consolidated and forecast revenue) in just one measure.

Thanks in advance for your help!

[v-deddai1-msft]

Hi @Anonymous ,

Would you please show us sample pbix and expected output?  I can't understand well based on your description.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi,

I can manage how to get the sum of the second column (uNetRev value), but my issue is on how to calculate the sum of the first column avoiding the rows where I already have figures on the second.

I guess I should use a date filter where the maximum date is the snapshot date-1 (in the example would be 19/7/20). Is that possible? Use a date origin from one calendar and date end from another? 

So the formula for that measure should be like :

Sum (Room Rev.) where date start = stay dates calendar start date and date end = snapshot calendar date -1 (this date will always be a single value)

[v-deddai1-msft]

Hi @Anonymous ,

Sorry I am still confused about your requirement without sample data and expected output.

Best Regards,

Dedmon Dai

[Anonymous]

Hi,

The problem I have is that the pbix file is huge and also contains confidential data, can't share that. 

[v-deddai1-msft]

Hi @Anonymous ,

Show me the sample data if possible.

Best Regards,

Dedmon Dai