Matching a date range with a sum of values from a related table(s)

[EugeneSledge]

I am looking for a solution to create a report table that originally contains start & end dates and it needs to get a summarized aggregation of costs from two related tables pulled in based on the selected interval in the target table. All the tables are connected by a common key. For a better understanding of the solution required - see a visualization of the target table and relationships of the model attached to post. many thanks in advance for any relevant help!

 

 

[EugeneSledge]

Many thanks Miguel, your suggested measure worked just fine! thanks again! It was a bit unusual and confusing to see double ampersand "&&" in the syntax but it works just the way I wanted! Thanks again!

[MFelix]

Hi @EugeneSledge ,

The double ampersand is the equivalent to the AND expression if you do the double vertical pipe "||" it's the equivalent of the OR.

[MFelix · Accepted solution]

Hi @EugeneSledge ,

First of all let me share some toughts on the setup you have, because my experience on many-to-many relationships with cross filtering is always difficult to control the final result of the calculations even more when you have more than one table in that situation.

This will cause you model to filter from left to right and right to left and you can have a lot of inconsistent results.

In this case you can try to add a measure similar to this one:

Total Cost1 =
CALCULATE (
    SUM ( Cost1[Amount] ),
    FILTER (
        ALLSELECTED ( Cost1[Date] ),
        Cost1[Date] >= SELECTEDVALUE ( Trips[StartDate] )
            && Cost1[Date] <= SELECTEDVALUE ( Trips[EndDate] )
    )
)

If thids does not work can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.