How to calculate value for specific date and use it in other date entries

[PowerBoynzzz]

I need to calculate a value for specific date, and use it across all the other dates. 

To calculate that value I wrote this:

Rate (Start Date) =

VAR _StartDate = 
    MAX('Aux Calendar'[Start Date])


RETURN
    CALCULATE (
        AVERAGE ('Rates Monthly'[Mid Rate] ),
        'Rates Monthly'[Date] = _StartDate
) 

This produces the correct value, but it needs to be constant in a subset of 'Rates Calendar'[Effective Date].

Instead it only returns value when the calendar date matches the _StartDate 

How can I achieve that the value 18.99 is returned in each of the desired 'Rates Calendar'[Effective Date] rows?

For example, lets say I want 18.99 to appear only from 01.01.2003 to 01.01.2005. 

This is the little model I'm using:

Thank you very much I know this is such a basic question. 

[v-yaningy-msft]

Hi, @PowerBoynzzz 

Thanks for the reply from govind_021 and Vallirajap. To achieve your need faster, you can share the pbix file without sensitive data (use GoogleDrive, OneDrive .etc to share the link), the desired result to achieve and the logic to achieve the result.

Here are some notes:
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Best Regards,
Yang
Community Support Team

[govind_021]

Hi @PowerBoynzzz 
Create Calculated Column , it will return same value for each row in the table and use that column in the table visual 
or you can create measure Max( calcualted column ) , it will give you the same value irrespective of the current filter context in your visual.

Regards
Govind Sapkade ( Data Analyst , Power BI PL 300 Certified , MS Fabric Enthusiast )
Linkdin : www.linkedin.com/in/govind-sapkade-845104225
Youtube : http://www.youtube.com/@govind_dataanalyst

[Vallirajap]

Hii @PowerBoynzzz, 

Try this DAX below. I give that solution based on my understanding. You try to describe a question in a simple and understandable way that will be helpful for community resolvers to reply.

Rate (Start Date) =

VAR _StartDate = 
    MAX('Aux Calendar'[Start Date])


RETURN
    CALCULATE (
        AVERAGE ('Rates Monthly'[Mid Rate] ),
        'Rates Monthly'[Date] = _StartDate,
        All(<TableName>[Effective Date])
) 

Dax Function, 

ALL([<table> | <column>) Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied.

I thought the solution was helpful to you, 
If the solution works, mark my post as a solution. That will be helpful to the needed one!