Help with summing values of open opportunities

[dsj8wksnnckk]

I have a table "Opportunities" that have the following columns "Unique Opportunity ID", "Created on", "Closed on", "Values" I need a DAX that will go through my date table and sum all the values of opportunities on that exact date that have a date "Created on" that is before and a "Closed on" that is after.

The active relationship between the "Date Table" and the "Opportunities" table is via "Created on" column


This is what I've come up with but it doesn't give me the right result. Im assuming its because of the active relationship (that must remain active)


Total Values =
SUMX( FILTER( Opportunities, Opportunities[Created on] <= MAX('Date'[Date]) && Opportunities[Closed on] >= MAX('Date'[Date]) ), Opportunities[Values] )

[v-cgao-msft · Accepted solution]

Hi @dsj8wksnnckk ,

Please try:

Total Values = 
CALCULATE(
    SUM(Opportunities[Values]),
    Opportunities[Created on] <= MAX('Date'[Date]),
    Opportunities[Closed on] >= MAX('Date'[Date]),
    USERELATIONSHIP('Date'[Date], Opportunities[Closed on])
)

Model:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[dsj8wksnnckk · Accepted solution]

Hey,

I've solved this using this logic: 
https://www.daxpatterns.com/events-in-progress/

[v-cgao-msft · Accepted solution]

Hi @dsj8wksnnckk ,

Please try:

Total Values = 
CALCULATE(
    SUM(Opportunities[Values]),
    Opportunities[Created on] <= MAX('Date'[Date]),
    Opportunities[Closed on] >= MAX('Date'[Date]),
    USERELATIONSHIP('Date'[Date], Opportunities[Closed on])
)

Model:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum