cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors