Forum Discussion

elmurat's avatar
elmurat
Icon for Advocate I rankAdvocate I
10 months ago
Solved

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

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.

 

  • 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 🙏

     

6 Replies

  • Aala_Ali's avatar
    Aala_Ali
    Icon for Most Valuable Professional rankMost Valuable Professional

    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 🙏

     

    • elmurat's avatar
      elmurat
      Icon for Advocate I rankAdvocate I

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

  • Aala_Ali's avatar
    Aala_Ali
    Icon for Most Valuable Professional rankMost Valuable Professional

    Hi elmurat 

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

  • v-achippa's avatar
    v-achippa
    Icon for Community Support rankCommunity Support

    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's avatar
      v-achippa
      Icon for Community Support rankCommunity Support

      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