Latest Closing Balance

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