Dynamic column calculation based on date range selector

[isaacmunoz]

Hello everyone!

I'm posting this message because I have been facing a problem this last days that I'm not able to solve. I have looked for an answer on the forums but unfortunately I didn't found for my case. So I would be pleased if you can give some help about how to solve it or have the confirmation that is impossible to do in PowerBI what I want to achieve.

What I want to achieve:
I trying to construct a report based in a data model that contains different opportunities and different reference dates for each opportunity. 

The main purpose of this report is to obtain the minimum (oldest) reference date for each opportunity when selecting a date range. Therefore it should be a dynamic calculation. To do so, I have included a date range selector based on a Date table linked with the reference dates:

Problem:
Altough I have created a solution that works statically:

The problem comes up when I select a date as minimum date range that is more recent than the first reference date of an opportunity. In this case, in the table the reference dates by opportunities will appear filtered by the date range selector but the calculation of the minimum reference date will not be dynamically recalculated:

For both the "DataHub" and "GoQu opportunity it should display 13/06/2022 as the min reference date. But, instead it displays the one that works when there is no date filter applied.

The code that I used for the "Min reference date" calculated column is:

Min reference date = 
VAR opportunity = [Opportunity]
var calc = CALCULATE(MIN('Open Opportunities'[Reference Date]); FILTER('Open Opportunities'; 'Open Opportunities'[Opportunity] = opportunity && 'Open Opportunities'[Reference Date] >= VALUE(MIN('Date'[Date]))))
return calc

Hope you can help me! Thanks in advance!

[v-cgao-msft · Accepted solution]

Hi @isaacmunoz ,

Please try this measure.

Min reference date = 
VAR _min_date =
    MIN ( 'Date'[Date] )
VAR _max_date =
    MAX ( 'Date'[Date] )
VAR _result =
    CALCULATE (
        MIN ( 'Open Opportunities'[Reference Date] ),
        FILTER (
            ALL ( 'Open Opportunities' ),
            'Open Opportunities'[Opportunity] = MAX ( 'Open Opportunities'[Opportunity] )
                && 'Open Opportunities'[Reference Date] >= _min_date
                && 'Open Opportunities'[Reference Date] <= _max_date
        )
    )
RETURN
    _result

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