Forum Discussion
Latest Closing Balance
Hello,
I need help with a formula to pull the latest closing balance by facility code/value date/report date.
Please see screenshot below for example.
For portfolio code 12345, the latest closing balance as of Report Date (9/19/2025) and latest Value Date (8/20/2025) is -3,224,059.05.
I would like the new column to show a closing balance of $3,244,059.05
Please note, my power bi table contains 1 slicer (dropdown) which is the Report Date.
Hi gmasta1129 ,
Thank you for reaching out to the Microsoft Fabric community forum.
I appreciate your update. I understand the measure is still not returning the expected results, even after trying different methods. This issue often happens because Power BI cannot identify a single report date or value date within the filter context when both are evaluated together. To resolve this, ensure your slicer uses a separate table containing only distinct report dates, and that this table has an active one-to-many relationship with your main Balances table on the Report Date column. Once set up, create the measure in the Balances table using the formula below. This setup allows Power BI to correctly identify the latest Value Date for each facility under the selected Report Date and return the corresponding closing balance.
Here is the DAX:
Latest Closing Balance =
VAR SelectedReport = SELECTEDVALUE('ReportDates'[Report Date])
VAR CurrentFacility = MAX('Balances'[Facility Code])
VAR LatestValueDate = CALCULATE(
MAX('Balances'[Value Date]),
FILTER(
ALL('Balances'),
'Balances'[Facility Code] = CurrentFacility && 'Balances'[Report Date] = SelectedReport
)
)
RETURN
CALCULATE(
MAX('Balances'[Closing Bal]),
FILTER(
ALL('Balances'),
'Balances'[Facility Code] = CurrentFacility && 'Balances'[Report Date] = SelectedReport && 'Balances'[Value Date] = LatestValueDate
)
)After making these changes, use the Report Dates table in your slicer and check the table visual again. You should now see the correct latest closing balance for each facility, based on the most recent value date under the selected report date. For example, selecting Report Date 09/19/2025 should show Facility Code 12345 as -3,224,059.05 and Facility Code 67890 as -500, matching the expected results. This ensures the slicer and measure work together as intended.
Please see the attached PBIX and screenshot file for reference.
Best Regards,
Tejaswi
Community Support
9 Replies
Hi, in the data sample, I can see all the report dates are same. However based on the sample provided, created a sample dataset.
Then created the below measure:
Latest Balance =CALCULATE(MAX(Balance[Closing Bal]),FILTER(Balance,Balance[Report Date] = MAX(Balance[Report Date]) &&Balance[Value Date] = MAX(Balance[Value Date])))This returns the latest value.Hope this helps to resolve your problem. If it does, then please mark it as solution.thanks - SamratHI gmasta1129 ,
I would also take a moment to thank mdaatifraza5556 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
Hi gmasta1129 ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you,Tejaswi.
Hi gmasta1129 ,
Just checking in have you been able to resolve this issue? If so, it would be greatly appreciated if you could mark the most helpful reply accordingly. This helps other community members quickly find relevant solutions.
Thank you.
Hi gmasta1129
Can you please try the below dax to create a measure ?
Latest Closing Balance =
VAR SelectedReportDate =
MAX('Data'[Report Date])VAR LatestValueDate =
CALCULATE(
MAX('Data'[Value Date]),
'Data'[Report Date] = SelectedReportDate
)VAR LatestBalance =
CALCULATE(
MAX('Data'[Closing Bal]),
'Data'[Report Date] = SelectedReportDate,
'Data'[Value Date] = LatestValueDate
)RETURN
LatestBalance
If this answers your questions, kindly accept it as a solution and give kudos.Hello mdaatifraza5556 ,
Thank you for the quick response but unfortunately, the formula did not work. The new column is pulling each closing balance (it looks exactly like the closing bal column) and not the last balance from value date 8/20/2025. I was expecting to see -3,224,059.05 in each row of the column.
Hi gmasta1129 ,
Thank you for reaching out to the Microsoft Fabric community forum.
I appreciate your update. I understand the measure is still not returning the expected results, even after trying different methods. This issue often happens because Power BI cannot identify a single report date or value date within the filter context when both are evaluated together. To resolve this, ensure your slicer uses a separate table containing only distinct report dates, and that this table has an active one-to-many relationship with your main Balances table on the Report Date column. Once set up, create the measure in the Balances table using the formula below. This setup allows Power BI to correctly identify the latest Value Date for each facility under the selected Report Date and return the corresponding closing balance.
Here is the DAX:
Latest Closing Balance =
VAR SelectedReport = SELECTEDVALUE('ReportDates'[Report Date])
VAR CurrentFacility = MAX('Balances'[Facility Code])
VAR LatestValueDate = CALCULATE(
MAX('Balances'[Value Date]),
FILTER(
ALL('Balances'),
'Balances'[Facility Code] = CurrentFacility && 'Balances'[Report Date] = SelectedReport
)
)
RETURN
CALCULATE(
MAX('Balances'[Closing Bal]),
FILTER(
ALL('Balances'),
'Balances'[Facility Code] = CurrentFacility && 'Balances'[Report Date] = SelectedReport && 'Balances'[Value Date] = LatestValueDate
)
)After making these changes, use the Report Dates table in your slicer and check the table visual again. You should now see the correct latest closing balance for each facility, based on the most recent value date under the selected report date. For example, selecting Report Date 09/19/2025 should show Facility Code 12345 as -3,224,059.05 and Facility Code 67890 as -500, matching the expected results. This ensures the slicer and measure work together as intended.
Please see the attached PBIX and screenshot file for reference.
Best Regards,
Tejaswi
Community Support
Hi gmasta1129, latest closing balance based on Facility Code and Report Date:
Add as separate step:
= Table.Combine(Table.Group(ChangedType, {"Facility Code", "Report Date"}, {{"T", each Table.AddColumn(_, "Latest Closing Bal", (x)=> Table.Last(_)[Closing Bal], Currency.Type), type table}})[T])
