topic Re: Use latest date available if selected date doesn't have data in DAX Commands and Tips

Use latest date available if selected date doesn't have data

elmurat — Tue, 28 Oct 2025 17:53:56 GMT

Hello,

I have two datasets:

1. Year End

2. Month End

My report has two Date Dropdown filters, Year End and Month End.

The calculation should display the difference in Principal for a loan ID for the selected Month-End and Year-End.

If for the selected Month End Date there is no data available, choose the latest available date.

So in the report, for example for the Loan2, is user selects 1/3/2025 for Year End and 9/30/2025 for Month End,

the principle should show 200. Because 1200-1000.

Any idea how to tackle this?

I really appreciate any help you can provide.

Re: Use latest date available if selected date doesn't have data

Aala_Ali — Wed, 29 Oct 2025 18:48:26 GMT

Hi elmurat

If i understand you right , first make sure your slicers is single-select and make the measure fall back to the latest date on or before the selection for the current LoanID.
First : Read the slicers
Selected Year-End Date =MAX ( YearEnd[YearEndDate] )

Selected Month-End Date =MAX ( MonthEnd[MonthEndDate] )

2nd: “Last available date ≤ selection” (keeps LoanID filter)
Last Available Year-End Date =
VAR sel = [Selected Year-End Date]
VAR dWithinOrBeforeSel =
CALCULATE (
MAX ( YearEnd[YearEndDate] ),
ALL ( YearEnd[YearEndDate] ),
YearEnd[YearEndDate] <= sel
)
VAR dFallbackOverallMax =
CALCULATE ( MAX ( YearEnd[YearEndDate] ), ALL ( YearEnd[YearEndDate] ) )
RETURN COALESCE ( dWithinOrBeforeSel, dFallbackOverallMax )

Last Available Month-End Date =
VAR sel = [Selected Month-End Date]
VAR dWithinOrBeforeSel =
CALCULATE (
MAX ( MonthEnd[MonthEndDate] ),
ALL ( MonthEnd[MonthEndDate] ),
MonthEnd[MonthEndDate] <= sel
)
VAR dFallbackOverallMax =
CALCULATE ( MAX ( MonthEnd[MonthEndDate] ), ALL ( MonthEnd[MonthEndDate] ) )
RETURN COALESCE ( dWithinOrBeforeSel, dFallbackOverallMax )

3rd : Principal “as-of” each side
Principal @ Year-End (as-of) =
VAR d = [Last Available Year-End Date]
RETURN
CALCULATE (
MAX ( YearEnd[Principal] ),
KEEPFILTERS ( YearEnd[YearEndDate] = d )
)

Principal @ Month-End (as-of) =
VAR d = [Last Available Month-End Date]
RETURN
CALCULATE (
MAX ( MonthEnd[Principal] ),
KEEPFILTERS ( MonthEnd[MonthEndDate] = d )
)

4th: Final difference
Principal Diff (MonthEnd – YearEnd) =
VAR m = [Principal @ Month-End (as-of)]
VAR y = [Principal @ Year-End (as-of)]
RETURN IF ( NOT ISBLANK(m) && NOT ISBLANK(y), m - y )

Same idea mentioned in this problem :
https://community.fabric.microsoft.com/t5/Power-Query/Find-Most-Recent-Date-Relative-to-another-Date-Column-in-Power/m-p/4790379

If this helps please mark my answer as Solution and drop a Kudos 🙏

Re: Use latest date available if selected date doesn't have data

Aala_Ali — Wed, 29 Oct 2025 18:51:51 GMT

Hi elmurat

I hope the link and my answer help address your question. Let me know if you encounter any issues.

Re: Use latest date available if selected date doesn't have data

v-achippa — Thu, 30 Oct 2025 19:12:31 GMT

Hi elmurat,

Thank you for reaching out to Microsoft Fabric Community.

Thank you Aala_Ali for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

Re: Use latest date available if selected date doesn't have data

v-achippa — Mon, 03 Nov 2025 12:20:01 GMT

Hi @elmurat,

We wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

Re: Use latest date available if selected date doesn't have data

elmurat — Mon, 03 Nov 2025 19:22:27 GMT

Thank you very much, Aala. Your solution helped me a lot.

Re: Use latest date available if selected date doesn't have data

Aala_Ali — Tue, 04 Nov 2025 08:58:00 GMT

Happy for helping , Welcome.