Measure based on two relationships

[Anonymous]

Hello,

I have datamodel with two tables: factValues and dimDate. There are 2 relationships between the tables. 

I want to create a measure, which will sum Value for each date by looking if it's between DateFrom and DateTo. My idea was to create measure as below, which unfortunetly does not work, because it consider only active relationship. 

Wartość Brutto Magazyn = 
CALCULATE (
    SUM ( WAP_fStanMagazynowy[wartoscNaDzienBrutto] ),
    AND(dDate[Date] >= MIN ( WAP_fStanMagazynowy[DataFrom] ),
   dDate[Date] <= MAX ( WAP_fStanMagazynowy[DataTo] ))
)

Any advice? Many Thanks, Kamil

[amitchandak]

@Anonymous , the expected output is not clear. Need  example

but refer

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

or

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

[Anonymous]

Expected result is equivalent of the excel formula below, also presented on screen. (@Date is column J).

=SUMIFS(E:E;C:C;"<="&[@Date];D:D;">="&[@Date])

[amitchandak · Accepted solution]

@Anonymous , the second link should have worked. That was measure-based, File for a table-based solution using my data, refer if that can help. Do not allocate 

file attached after signature

[Anonymous]

Thanks Amit,

helpful as always, huge cheers! 🙂