Showing results for 
Search instead for 
Did you mean: 

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

Not applicable

Combine sum of two tables depending on dates



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. 

Screenshot at Jul 21 16-58-02.png

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


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

Community Support
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

Not applicable



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)

Hi @Anonymous ,


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


Best Regards,

Dedmon Dai

Not applicable



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

Hi @Anonymous ,


Show me the sample data if possible.


Best Regards,

Dedmon Dai


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Dashboard in a day with date

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!

Power BI Fabric Summit Carousel

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