Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
1 year ago
Solved

Help with Date Filter needed

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 Numbe...
Need Help
Show and Tell
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    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!



     

burakkaragoz's avatar
burakkaragoz
Icon for Super User rankSuper User
1 year ago

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's avatar
    ArchStanton
    Icon for Power Participant rankPower Participant
    1 year ago

    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?