Issue in Cumulative Measure

[yashwant101]

Hi all,

I have created a group of measures to take the count of patients whose last status were active:

Last Status =

VAR _MaxDt = CALCULATE(MAX(rpt_ecd_perf[txn_dt]),ALL(rpt_ecd_perf[ptnt_stat]))

VAR _MaxStat = CALCULATE(MAX(rpt_ecd_perf[ptnt_stat]), rpt_ecd_perf[txn_dt]=_MaxDt)

RETURN

_MaxStat

AP Logic P =

CALCULATE(

    DISTINCTCOUNT(rpt_ecd_perf[ptnt_id]),

    FILTER(

        SUMMARIZE(rpt_ecd_perf,rpt_ecd_perf[ptnt_id],

        "last status",

        [Last Status]),

        [last status]="ACTIVE"))

Patient Count P =

VAR SelectedPeriod = SELECTEDVALUE('time_period'[Time Period])

VAR total =

    CALCULATE(

         [AP Logic P],

       --  (rpt_ecd_perf[Patient ID])<> BLANK(),

         All(rpt_ecd_perf[txn_dt]) ,

         rpt_ecd_perf[txn_dt]<=MAX(rpt_ecd_perf[txn_dt]),

         ALL(rpt_ecd_perf[Time Period]),

         ALL(rpt_ecd_perf[Time Period Sort])

    )

RETURN

total

Thime Period column in above measure is a month column created based on txn_dt.

Now the problem is in monthly graphs, when a record for patient does not appear in a particular month it is not counted there even if the last status in the previous month for the Patient was Active.

Any help is appreciated.

Regards,

Yashwant

[v-sshirivolu]

Hi @yashwant101 ,
I would also take a moment to thank @FarhanJeelani , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[v-sshirivolu]

Hi @yashwant101   ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-sshirivolu]

Hi @yashwant101 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[Shahid12523]

Your current measure only counts patients with transactions in the current month. To fix that, you need to evaluate each patient’s last known status up to the selected month, even if they have no record that month.
✅ Fix:
Use a disconnected date table and rewrite your logic to find each patient’s latest status before or on the selected date. Then count those with "ACTIVE" status.
This way, your monthly graph will include patients who were still active but didn’t have a transaction that month.

[FarhanJeelani · Accepted solution]

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.