Forum Discussion
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/4790379If this helps please mark my answer as Solution and drop a Kudos 🙏
6 Replies
- Aala_Ali
Most 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/4790379If this helps please mark my answer as Solution and drop a Kudos 🙏
- v-achippa
Community 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