Measure not returning the LATEST value in category

[awg201]

Hi, I have written a measure which aims to count the number of cases that were open as at a selected period (financial year starting JULY) from a slicer. It counts correct when used by itself, but when I add the location column in, it seems to double up numbers if a case has multiple locations in the same period.

Below is a very small example data:

CaseID	CaseStartDate	CaseEndDate	FinaliseDate	LocationName
A	17-Dec-24	19-Dec-24		SYDNEY
A	11-Sep-24	11-Sep-24		SYDNEY
A	11-Feb-25	13-Feb-25		MELBOURNE
A	26-Apr-24	26-Apr-24		SYDNEY

Here is the DAX measure i have written:

Count_OpenCases = 
VAR MaxDate = MAX('DateTable'[Date])

-- aggregated case dates in period
VAR CaseSummary =
    ADDCOLUMNS(
        SUMMARIZE(
            CasesTable,
            CasesTable[CaseID]
        ),
        "EarliestCaseDate", CALCULATE(MIN(CasesTable[CaseStartDate])),
        "LatestFindingsDate", CALCULATE(MAX(CasesTable[FindingsDate])),
        "LastLocation", 
            CALCULATE(
                SELECTCOLUMNS(
                    TOPN(1, 
                        FILTER(
                            CasesTable, 
                            CasesTable[CaseStartDate] <= MaxDate
                        ), 
                        CasesTable[CaseStartDate], DESC
                    ),
                    "Location", CasesTable[LocationName]
                )
            )
    )

-- get cases still open as at selected period
VAR OpenCases =
    FILTER(
        CaseSummary,
        [EarliestCaseDate] <= MaxDate &&
        (
            ISBLANK([LatestFindingsDate]) || [LatestFindingsDate] > MaxDate
        )
    )

RETURN COUNTROWS(OpenCases)

When i slice for 2025, it returns 1, which is correct, but in a table visual (or charts), i get:

LocationName	Count_OpenCases	CaseID
MELBOURNE	1	A
SYDNEY	1	A
TOTAL	1

I have a rough understanding of why this is showing, as technically , they are both present in FY2025 (Jul 24 to Jun25), but I want  it to only show Melbourne as this is the latest location based on the CaseStartDate.

When run the CaseSummary in DAX query view, the table produced seem to get the correct results with my input year, with the locations dynamically updated.

How can get this in a visual:

LocationName	Count_OpenCases	CaseID
MELBOURNE	1	A

Please help!

[bhanu_gautam]

@awg201 , Try using

dax
Count_OpenCases =
VAR MaxDate = MAX('DateTable'[Date])

-- aggregated case dates in period
VAR CaseSummary =
ADDCOLUMNS(
SUMMARIZE(
CasesTable,
CasesTable[CaseID]
),
"EarliestCaseDate", CALCULATE(MIN(CasesTable[CaseStartDate])),
"LatestFindingsDate", CALCULATE(MAX(CasesTable[FindingsDate])),
"LastLocation",
CALCULATE(
SELECTCOLUMNS(
TOPN(1,
FILTER(
CasesTable,
CasesTable[CaseStartDate] <= MaxDate
),
CasesTable[CaseStartDate], DESC
),
"Location", CasesTable[LocationName]
)
)
)

-- get cases still open as at selected period
VAR OpenCases =
FILTER(
CaseSummary,
[EarliestCaseDate] <= MaxDate &&
(
ISBLANK([LatestFindingsDate]) || [LatestFindingsDate] > MaxDate
)
)

-- get the latest location for each case
VAR LatestLocationCases =
ADDCOLUMNS(
OpenCases,
"LatestLocation", [LastLocation]
)

RETURN
COUNTROWS(
FILTER(
LatestLocationCases,
LatestLocationCases[LastLocation] = MAX(CasesTable[LocationName])
)
)

[awg201]

That was a speedy response! Thanks for that

I had tried a similar variation, but i seem to get the similar error "Cannot find table 'LatestLocationCases'.". It just doesn't seem to let me reference any virtual tables created as variables.

[v-saisrao-msft]

Hi @awg201,

Thank you for reaching you ti the Microsoft Forum Community.

I have created a measure that uses ranking based on CaseEndDate. You can adjust the ranking logic to suit your specific requirements and apply the necessary filtering as shown below

Attached the Pbix file for your reference.

If you need help please share the additional data to provide exact solution.

[awg201]

Hi @v-saisrao-msft , thanks for your help

For some reason, when I apply this to a larger dataset, it just returns a single row with an unexpected result.

So I tried it in your example by adding more data, and got the same outcome:

Here is the expanded DATASET:

CaseID	CaseStartDate	CaseEndDate	LocationName
A	17-Dec-24	19-Dec-24	SYDNEY
A	11-Sep-24	11-Sep-24	HOBART
A	11-Feb-25	13-Feb-25	MELBOURNE
A	26-Apr-24	26-Apr-24	SYDNEY
B	01-Jan-24	06-Jan-24	BRISBANE
B	03-Jun-23	08-Jun-23	SINGAPORE
C	12-Feb-24	16-Feb-24	TEXAS
C	15-Mar-24	18-Mar-24	NEW YORK
D	22-Jul-24	25-Jul-24	LONDON
D	05-Nov-24	07-Nov-24	PARIS
E	10-Aug-24	12-Aug-24	TOKYO
E	19-Sep-24	21-Sep-24	BERLIN
F	02-Oct-24	05-Oct-24	SYDNEY
F	14-Dec-24	16-Dec-24	MELBOURNE
G	23-Jan-25	25-Jan-25	BRISBANE
G	08-Apr-25	10-Apr-25	SINGAPORE
G	23-Jun-25	25-Jun-25	NEW ZEALAND
H	17-May-25	19-May-25	TEXAS

Below would be the desired result:

Sliced for 2024

CaseID	CaseStartDate	CaseEndDate	LocationName
A	17-Dec-24	19-Dec-24	SYDNEY
B	01-Jan-24	06-Jan-24	BRISBANE
C	15-Mar-24	18-Mar-24	NEW YORK
D	05-Nov-24	07-Nov-24	PARIS
E	19-Sep-24	21-Sep-24	BERLIN
F	14-Dec-24	16-Dec-24	MELBOURNE

for 2025:

CaseID	CaseStartDate	CaseEndDate	LocationName
A	11-Feb-25	13-Feb-25	MELBOURNE
G	23-Jun-25	25-Jun-25	NEW ZEALAND
H	17-May-25	19-May-25	TEXAS

it would proably need to be dynamic as we may need to drill down into months as well... thanks again for your help. I've spent way too long trying to ge this to work..

[v-saisrao-msft]

Hi @awg201,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @awg201,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft]

Hi @awg201,

Please refer the below attached .PBIX file

Thank you

[awg201]

@v-saisrao-msft thanks again. I think this would have to be a dynamic measure rather than a column. Essentialy, a case should only be counted once in a period, whether it be a month or a year.

I think what you had initally was close, but for some reason, it did not like it when a larger dataset was used.

CaseID	CaseStartDate	CaseEndDate	LocationName
A	17-Dec-24	19-Dec-24	SYDNEY
A	11-Sep-24	11-Sep-24	HOBART
A	11-Feb-25	13-Feb-25	MELBOURNE
A	26-Apr-24	26-Apr-24	SYDNEY
B	01-Jan-24	06-Jan-24	BRISBANE
B	03-Jun-23	08-Jun-23	SINGAPORE
C	12-Feb-24	16-Feb-24	TEXAS
C	15-Mar-24	18-Mar-24	NEW YORK
D	22-Jul-24	25-Jul-24	LONDON
D	05-Nov-24	07-Nov-24	PARIS
E	10-Aug-24	12-Aug-24	TOKYO
E	19-Sep-24	21-Sep-24	BERLIN
F	02-Oct-24	05-Oct-24	SYDNEY
F	14-Dec-24	16-Dec-24	MELBOURNE
G	23-Jan-25	25-Jan-25	BRISBANE
G	08-Apr-25	10-Apr-25	SINGAPORE
G	23-Jun-25	25-Jun-25	NEW ZEALAND
H	17-May-25	19-May-25	TEXAS

If we just focus on Case A, when I slice for 2024 for example, I should see:

CaseID	CaseStartDate	CaseEndDate	LocationName
A	17-Dec-24	19-Dec-24	SYDNEY

but when i further filter or drill for the month Febraury in 2024, i should only get

CaseID	CaseStartDate	CaseEndDate	LocationName
A	11-Feb-25	13-Feb-25	MELBOURNE

[v-saisrao-msft]

Hi @awg201,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi @awg201,

Please refer the below atached .PBIX file.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.