Fixed date filter of values with 2 date columns

[astarkey]

Any help appreciated
I have a table that contains multiple columns of equipment data including a contract start column and a contract end column
I want to use these as a fixed date range to show other records...
For example revenue during this contract period and costs during this contract period

This then needs to go into a matrix with a row for each live of equipment showing the data for their contract period

I've tried a few things but no success, seems like it should be straight forward.....

Cheers

[Greg_Deckler]

Sample data and expected results would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That being said, you often need to use a disconnected date table from your main fact table to achieve what you want. Also, you might check out these two Quick Measures where I do some interesting things with date ranges, Periodic Billing and Open Tickets. They may help with your situation.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

[astarkey]

This is what I have, all the values to the right of contract end date are filtered by a slicer on the page, returning the values based on the filtered date range (exactly what I wanted) however I would also like to have a report page that only looks at the data within each o the rows contract dates, start to finish (or current day)

The contract start date and end date sit in seperate columns within one of my database tables.

Not sure if this helps anyone a bit more towards a solution....

I'm guessing I will need to link the contract start and end dates to my date table somehow, even though both of these dates are in seperate columns within the same database table...

Cheers

[Seward12533]

Can you provide a better idea of what you want the report to look like? The solution will really depend on your table layout.  As @Greg_Deckler suggested a disconnected slicer for to select the target date and then a conditional test.  You can link two dates from the same table to your date table but only one can be active at a time.  The first you create will be active by default and if you want to use the other relationship you need to specify the relationship using USERELATIONSHIP as a filter term on on a calculate

[Seward12533]

Try something like this

Revenue = VAR End_Date = MAX(contracttable[Contract End]) VAR Start_Date = MIN(contracttable[Contract Start]) RETURN 
CALCULATE(SUM([Sales]),CalendarTable[day]<=End_Date&&CalendarTable[day]>=Start_Date)

Might need to do a FILTER in the calculate. 

Revenue = VAR End_Date = MAX(contracttable[Contract End]) VAR Start_Date = MIN(contracttable[Contract Start]) RETURN 
CALCULATE(SUM([Sales]),FILTER(CalendarTable,CalendarTable[day]<=End_Date&&CalendarTable[day]>=Start_Date))

Didn't test this but something close should work. 

Note if you need to block this in the summary columns of the table use ISFILTERED in an IF or HASONEVALUE to test so it only calculates if the row is for a single piece of equipment. 

Revenue only If one piece of equipment = IF(HASONEVALUE(equiptable[EquipID]),[REVENUE])