YTD Actuals Drill through filter works but not showing the net accounted amount

[carolineh23]

Aim: Drill down to the transactional detail of a YTD balance
I have a summary table showing Year-to-Date Actuals by account code and have managed to create a YTD measure that picks up the financial year and month selected from the slicers on the page.

Actuals YTD (to selected month) =

VAR SelFY =

    SELECTEDVALUE ( 'Date Table'[FiscalYear] )

/* If a single month is selected, use its end date; otherwise use the max visible date */

VAR EndOfSelectedMonth =

    EOMONTH ( MAX ( 'Date Table'[Date] ), 0 )

RETURN

CALCULATE (

    [Net Acc Amount],

    FILTER (

        ALL ( 'Date Table' ),                            -- clear all date filters first

        'Date Table'[FiscalYear] = SelFY                 -- keep only the selected FiscalYear

            && 'Date Table'[Date] <= EndOfSelectedMonth  -- up to end of selected month

    )

)

I then created a column to filter on the months that make up the YTD month range with a '1'

IsInYTDRange =

VAR SelFY = 'Date Table'[FiscalYear]

VAR FYStart =

    CALCULATE (

        MIN ( 'Date Table'[Date] ),

        ALLEXCEPT ( 'Date Table', 'Date Table'[FiscalYear] )

    )

VAR FYEnd =

    EOMONTH ( 'Date Table'[Date], 0 )

RETURN

IF (

    'Date Table'[Date] >= FYStart &&

    'Date Table'[Date] <= FYEnd,

    1,

    0

)

This works for drill through to a transactional detail table and all is showing as I'd expect except the net accounted amount which is showing blank e.g. for Aug and Sep if Oct YTD was selected (our financial year starts Aug) - octobers is showing as expected.
Does anyone know why this would occur?

Thanks in advance, I am new to PowerBI

[v-sgandrathi]

Hi @carolineh23,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[carolineh23]

Thank you very much I have removed the column measure and it is working now 🙂

[v-sgandrathi]

Hi @carolineh23,

Glad you have resolved the issue and pease reach out to us if further if you facing any issues.

Thank you and continue using Microsoft Fabric Community Forum.

[v-sgandrathi · Accepted solution]

Hi @carolineh23,

This issue occurs because the IsInYTDRange calculated column affects the filter context on the drill-through page. Calculated columns are processed row by row and do not respond to slicer selections. When drilling through, each transaction date becomes the “end date” for its row, causing earlier months like August and September to fall outside the filter context used by the YTD measure. As a result, Net Acc Amount returns blank for those months, while the selected month (October) still displays a value.

To resolve this, it’s best not to use a calculated column for YTD filtering. Instead, place all YTD logic in a measure, which will respect slicers and drill-through context. Removing the IsInYTDRange column from the drill-through filters and relying only on the YTD measure will fix the problem. This approach follows standard Power BI practices for time-intelligence calculations.

Thank you.