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

[elmurat]

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.

[v-achippa]

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

[v-achippa]

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

[Aala_Ali]

Hi @elmurat 

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

[Aala_Ali · Accepted solution]

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...

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

[elmurat]

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

[Aala_Ali]

Happy for helping , Welcome.