Help with Date Filter needed

[ArchStanton]

Hi,

The code below produces the wrong values in my Matrix visual (the Resolution Table = is a Date - Calendar Table).

YTD Closed Cases 6 = 
CALCULATE (
    TOTALYTD ( COUNT ( 'Cases'[Case Number] ), ResolutionTable[Date], "31/03" ),
    'Cases'[statecode] = "Resolved"
)

Wrong Values because April values are being duplicated apart from the 2 new items in the 24-27mth age category.

The correct result is here = 

I used a different measure to obtain the correct result but it still needs a page filter that filters the Resolution Date >31 Mar 2025.
I would like to use a measure to obtain these values but without the need of a Date filter on the Page - can anyone help?

This measure (Closed Cases 3) works but only with a Date Filter on 'Cases' [Resolution Date] > 31/03/2025

YTD Closed Cases 3 = 
CALCULATE (
    TOTALYTD ( COUNT ( 'Cases'[Case Number] ), 'Cases'[Resolution Date], "31/03" ),
    'Cases'[statecode] = "Resolved"

Thanks

[DataNinja777]

Hi @ArchStanton ,

The issue arises because you're using ResolutionTable[Date] in your TOTALYTD calculation, but the actual resolution dates of the cases are in the 'Cases'[Resolution Date] column. Unless there's an active one-to-many relationship between 'Cases'[Resolution Date] and ResolutionTable[Date], the date filter context won't propagate correctly, leading to duplicated April values. The measure that gave you correct results—YTD Closed Cases 3—works because it references the resolution date column directly but only works when a page-level filter is applied.

To solve this and remove the dependency on a page filter, modify the measure to embed the resolution date condition directly within the CALCULATE function. Use the version below to count only resolved cases with resolution dates after 31 March 2025, applying the correct year-to-date logic.

YTD Closed Cases Fixed = 
CALCULATE(
    TOTALYTD(
        COUNT('Cases'[Case Number]),
        'Cases'[Resolution Date],
        "31/03"
    ),
    'Cases'[statecode] = "Resolved",
    'Cases'[Resolution Date] > DATE(2025,3,31)
)

If you need to continue using the calendar table for consistency, ensure there's an active relationship between 'Cases'[Resolution Date] and ResolutionTable[Date]. Then the following version will also work without requiring an external filter:

YTD Closed Cases Using Calendar = 
CALCULATE(
    TOTALYTD(
        COUNT('Cases'[Case Number]),
        ResolutionTable[Date],
        "31/03"
    ),
    'Cases'[statecode] = "Resolved",
    'Cases'[Resolution Date] > DATE(2025,3,31)
)

This approach avoids duplicated values and gives you the correct output directly from the measure.

Best regards,

[ArchStanton]

Hi, thanks for your reply. The 'using calendar' measure give me the same duplication as before. Is there a way of writing this measure just using the TOTALYTD function with Year Ending "31/3"?

Also, the ResolutionTable has an active One to Many relationship with the Resolution Date in the Cases Table.

[v-aatheeque · Accepted solution]

Hi @ArchStanton,
First, create a base measure for Resolved Case Count:

Resolved Case Count = 
CALCULATE(
    COUNT('Cases'[Case Number]),
    'Cases'[statecode] = "Resolved",
    'Cases'[Resolution Date] > DATE(2025,3,31)
)

Then, use TOTALYTD on top of this base measure:

YTD Closed Cases FINAL = 
TOTALYTD(
    [Resolved Case Count],
    ResolutionTable[Date],
    "31/03"
)

Hope this helps !!

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!

[ArchStanton]

Thanks for this - much appreciated!

[burakkaragoz]

Hi @ArchStanton ,

The issue seems to be that your current measure is not respecting the additional filter for 'Cases'[Resolution Date] > 31/03/2025, which is why April values are being duplicated.

To fix this, you can explicitly apply the date filter inside your CALCULATE block. Try modifying your measure like this:

YTD Closed Cases =
CALCULATE (
    COUNTROWS ( 'Cases' ),
    FILTER (
        ALLSELECTED ( 'Resolution Table'[Date] ),
        ISONORAFTER ( 'Resolution Table'[Date], MAX ( 'Resolution Table'[Date] ), DESC )
    ),
    'Cases'[statuscode] = "Resolved",
    'Cases'[Resolution Date] > DATE(2025, 3, 31)
)

This way, the measure will only count resolved cases where the resolution date is after March 31, 2025, and it should prevent the April duplication you're seeing.

Let me know if this gives you the expected output or if we need to tweak it further.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[ArchStanton]

Thanks for the quick reply, I want the measure to calculate YTD values with my FY starting on 1st Apr hence the TOTALYTD function. I don't want to hardcode the date in a filter as in your example because I will need to manually update this next year, can you tweak your suggestion so I can still use TOTALYTD?