Max of Latest Date and Column Date

[Nadim1]

Hi all,

Trying to figure out how I can get the Max of: Latest Date in the series that is less than or equal to a date referenced in the column. For some context, I have a Query on a data feed of Exchange Rates - I want the latest exchange rate, but only up to the date specified. I would make Exchange Rate = Date Specified, although I don't want it to fail if there's no Exchange Rate available for that day (i.e. weekend/holiday). 

For example: Specified Date = October 22, latest Exchange Rate Data is October 27 (yesterday). So I want it to reference the October 22 rate (not October 27).

[v-yalanwu-msft]

Hi, @Nadim1 ;

My suggestion is to use the DAX formula, which can be used to find the latest date with a formula similar to the following;

LY DTD =
CALCULATE (
    MAX ( 'table'[Date] ),
    ALLEXCEPT ( 'Fiscal Year Conversion', 'Fiscal Year Conversion'[Group] )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Why would you want to do this if you can just use Power Query to fill in any gaps in rates using just one click of a button? This will not only be consistent but also will outperform any DAX solution by a long margin.

[Nadim1]

Can you confirm what you mean by this? I'm not sure how I would be able to leverage Power Query to fill in any gaps using one click.. 

[daXtreme]

This Fill is able to do what you want with just one mouse click...