Issue in Cumulative Measure

Hi yashwant101 ,

Your current Last Status measure returns the last status based on the very last txn_dt in the entire dataset (and even uses a MAX on the status on that date). That’s not per-patient and not anchored to the end of the month in your visuals.

When a patient has no activity in a given month, their “last status” for that month should be the status at their last transaction date up to the end of that month. Your current logic doesn’t do that, so they aren’t counted in months with no activity._
What you need

A calculation that, for the current month (or end date of the current period), finds for each patient:
their last transaction date <= end-of-month
the status on that exact date
Then count patients whose that status is ACTIVE.
A robust pattern (as of period end) Replace or augment your logic with a measure that computes per patient the last transaction date up to the period end, then checks the status on that date.

 

Active patients as of period end (per month)

This measure counts distinct patients whose last status up to the end of the current period is ACTIVE.

Active Patients (as of period end) = VAR EndDate = MAX(rpt_ecd_perf[txn_dt]) RETURN CALCULATE( DISTINCTCOUNT(rpt_ecd_perf[ptnt_id]), FILTER( VALUES(rpt_ecd_perf[ptnt_id]), VAR LastDate = CALCULATE( MAX(rpt_ecd_perf[txn_dt]), FILTER( rpt_ecd_perf, rpt_ecd_perf[ptnt_id] = EARLIER(rpt_ecd_perf[ptnt_id]) && rpt_ecd_perf[txn_dt] <= EndDate_

 

Please mark this post as solution if it helps you. Appreciate Kudos.