Disparity in Measures

[ArchStanton]

Hi,

I have two tables in my Data Model that contain case information:
1.'Cases'
2. 'Closed Cases'

'Cases' contains ALL Cases, regardless if the case is active or resolved.
'Closed Cases' only contains resolved cases.

'Cases' is joined to my Calendar Date Table by 'Cases' [Created On]  to Date [Dates]
'Closed Cases' is joined to my Calendar Date Table by 'Cases' [Resolution Date]  to Date [Dates]

I have a Closed Cases Measure that calculates the amount of Closed Cases YTD based on the main 'Cases' table that works:

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

= 

But when I try to replicate the measure using the 'Closed Cases' table =

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

I get this = 

Why is this happening? Does anyone know how I can modify this Closed Cases Table Measure so it produces the same results as the 1st chart?

Thanks,

[Ritaf1983]

Hi @ArchStanton 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

[ArchStanton]

Here is the link to the pbix file that contains dummy data. The situation has thankfully been replicated here - why is the closed cases measure not working?

https://www.dropbox.com/scl/fi/6bqq12c6phv40t9e3igsq/Dummy-Data.pbix?rlkey=opk5mz4jmclggfze8efdgjov6... 

[Ritaf1983 · Accepted solution]

Hi @ArchStanton 
The problem is that you took the team from the cases table table.
There is no relationship by the team between the cases and closed cases table so the measure gives you the values of all the teams :

If you take the team from closed cases this will resolve this issue :

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[ArchStanton]

Thank you - thats so obvious when you think about it!

[Ritaf1983]

Happy to help 🙂