Calculating Status count a at point in time

[Anonymous]

Hi folks,

Looking for help with DAX measure.

I have a requirment to get count of claims in pending status at a given point in time selected by the user. Below is the dataset, if user selects 30 the March 2024 from the date filter, I need to show how many claims were at pending status at that point in time. The table has the staus change date for the claim. As of 31st March there is 1 pending claim and as of 31st April there is 2 pending claim. need help with DAX to calculate this measure.

Claim No	Status Date	Status
123	4/12/23	Pending
123	4/02/24	Closed
123	4/03/24	Pending
123	4/04/24	Open
123	30/11/24	Closed
124	4/12/23	Pending
124	4/02/24	Closed
124	14/04/24	Pending
124	15/05/24	Open
124	20/11/24	Closed
125	15/04/24	Pending
125	1/06/24	Open

[Anonymous · Accepted solution]

Hi ,

Based on the description, the method rajendraongole1 provided should be helpful. 

Besides, you can also create a new calendar table.

Then, drag the calendar table column to the slicer visual. Try using the following DAX formula to calculate the status count.

Measure = 
VAR SelectedDate = MAX('Table'[Date])
RETURN
    CALCULATE(
        COUNTROWS('Claim'),
        FILTER(
            'Claim',
            'Claim'[Status Date] <= SelectedDate &&
            'Claim'[Status] = "Pending" )
    )

The result is shown below.

Best Regards,

Wisdom Wu

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

[divyed]

Hello @Anonymous ,

Considering you have a date table with proper relation setup, you can try below dax

PendingStatusCount =
VAR SelectedDate = MAX('Date'[Date]) -- Get the selected date from the slicer. ALso can try SelectedValue('Date'[Date])
RETURN
CALCULATE(
COUNTROWS(Claims),
Claims[Status] = "Pending",
Claims[Status_Date] <= SelectedDate, -- Filter for claims where the status date is on or before the selected date
ISBLANK(CALCULATE(MAX(Claims[Status_Date]), Claims[Claim_Number] = EARLIER(Claims[Claim_Number]))) -- Ensure the latest status is pending for the given claim
)

I hope this helps.

Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.

Cheers

[johnt75]

You can write a measure like

Num Pending at date = 
VAR FilteredTable = CALCULATETABLE(
	'Table',
	'Date'[Date] < MAX( 'Date'[Date] )
)
VAR PartitionedTable = CALCULATETABLE( 
	INDEX( 1, 
		FilteredTable,
		ORDERBY( 'Table'[Status Date], DESC ),
		PARTITIONBY( 'Table'[Claim No] ),
		MATCHBY( 'Table'[Claim No], 'Table'[Status Date] )
	),
	REMOVEFILTERS('Date')
)
VAR Result = COUNTROWS( FILTER( PartitionedTable, 'Table'[Status] = "Pending" ) )
RETURN Result

[rajendraongole1]

Hi @Anonymous - you can create below dax measure:

PendingClaimsCount =
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
VAR ClaimsWithPendingStatus =
FILTER(
'Claims',
'Claims'[Status Date] <= SelectedDate &&
'Claims'[Status] = "Pending" &&
NOT (
CALCULATE(
MAX('Claims'[Status Date]),
ALLEXCEPT('Claims', 'Claims'[Claim No]),
'Claims'[Status Date] > 'Claims'[Status Date] &&
'Claims'[Status Date] <= SelectedDate
) > 'Claims'[Status Date]
)
)
RETURN
COUNTROWS(ClaimsWithPendingStatus)

replace with your model table name and details. Hope this helps.