Relationship conflicts with a measure

[CarlsBerg999]

Hi,

The goal of the measure below is to distribute contract revenue across the days on which it is valid. For example, contract lenght is 3 months, so i want to show the value across three months, rather than at one point of time.

Total (Distributed) = CALCULATE(

SUMX(

SUMMARIZE(

FILTER(

CROSSJOIN('Table1',DateTable),

DateTable[Date]>=('Table1'[Invoice Date]) &&

DateTable[Date]<=('Table1'[Invoice Period End])),

'Table1'[Contract ID & Line Item ID],DateTable[Date],"SUM",sum('Table1'[Daily Value])),[SUM]))

The measure works as long as there is no relationship between the 'Table 1'[Invoice Date] and 'DateTable'[Date]. If i put the relationship, the measure works incorrectly and shows the value on 1 date rather than distributing it. Why does this happen and how do i change the measure above to allow the creation of a relationship? I need the relationship in other visuals of the report.

Thank you in advance!

[v-kelly-msft · Accepted solution]

Hi  @CarlsBerg999 ,

If you create  a relationship between the two tables,you could add "Related" function before ('Table1'[Invoice Date]) and ('Table1'[Invoice Period End]),if this doesn work,could you pls provide some sample data with expected output?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!