Forum Discussion
Calculating Status count a at point in time
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 |
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.
4 Replies
- rajendraongole1
Super User
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.
johnt75You 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- 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
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.
