cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Combine sum of two tables depending on dates

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.

5 REPLIES 5
Community Support

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
Not applicable

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)

Community Support

Hi @Anonymous ,

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

Best Regards,

Dedmon Dai

Anonymous
Not applicable

Hi,

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

Community Support

Hi @Anonymous ,

Show me the sample data if possible.

Best Regards,

Dedmon Dai

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors