How can I sum revenue between two date in different table?

[Silver75]

Hi all,

I have to sum for each ID the revenue in fact table with date greater than date in dimension 1

The two tables have a secondary relationship.

How can I resolve, please?

Thank you

I have attached an example with target of dax (REVENUE_MEASURE)

[v-huizhn-msft]

Hi @Silver75,

In your result table, the REVENUE sum should be 70 rather than 20, because 2015/10/10 is also greater than 2015/02/15 for ID 2. I try to reproduce your scenario and get expected result.

For your source table, create a one(FACT) to many(dimension) relationship as the screenshot below, right?



Then create a measure using the formula.

REVENUE_Measure = CALCULATE(SUM('FACT'[REVENUE]),FILTER(ALLEXCEPT('FACT','FACT'[ID]),'FACT'[DATE_ORDER]>RELATED(Dimension1[DATE])))

Finally, create a table visual, select the ID as axis level, the measure as value, you will get result, please see the following screenshot.

If you have any question, please let me know.

Best Regards,
Angelia