cumulative days for all benefits returning cumulative for selected benefit

[Mike_Yukon]

I have 3 measures. Users can use slicers to select the last benefit year, last benefit month, benefit type and benefit status. Benefit type and benefit status slicers allow the user to select multiple benefits and multiple statuses.

Report visual is a table with claim # and the 3 measures.

Sample claim from output shown (year=2022, month=12, all benefit types, all benefit statuses):

Claim No	LastScheduledBenefit	CumBenefitDaysSelectedVars	CumBenefitDaysSelectedBnftSts
1234	bnftB, 12/25/2022-12/31/2022, paid	585	585

LastScheduledBenefit returns a string of text that represents the last scheduled benefit in the selected month. Measure is dependent on all the slicer values. Seems to work as expected.

CumBenefitDaysSelectedVars returns the cumulative number of days of benefits -- this is dependent on all the slicer values. It also seems to work as expected.

CumBenefitDaysSelectedBnftSts returns the cumulative benefit days REGARDLESS of benefit type but SUBJECT to benefit status and year/month. This measure is NOT working as expected.

For example, if I choose bnftB as the Benefit Type, I expect to see this:

Claim No	LastScheduledBenefit	CumBenefitDaysSelectedVars	CumBenefitDaysSelectedBnftSts
1234	bnftB, 12/25/2022-12/31/2022, paid	405	585

But instead, I see this:

Claim No	LastScheduledBenefit	CumBenefitDaysSelectedVars	CumBenefitDaysSelectedBnftSts
1234	bnftB, 12/25/2022-12/31/2022, paid	405	405 (should be 585)

What am I doing wrong?

Code for the last measures is below:

CumBenefitDaysSelectedBnftSts =

/* cululative distinct days of all earnings loss, subject to month and status slicers only */

/* uses lookup tables */

VAR SelectedYear1 = SELECTEDVALUE('Indemnity Date Dimension'[Indemnity Date].[Year])

VAR SelectedMonth1 = SELECTEDVALUE('Indemnity Date Dimension'[Indemnity Date].[MonthNo])

VAR LastDayOfMonth1 = EOMONTH(DATE(SelectedYear1, SelectedMonth1, 1), 0)

VAR BenefitDaysSTSSum =

CALCULATE(

COUNTROWS('Indemnity Benefits'),

'Indemnity Date Dimension'[Indemnity Date].[Date] <= LastDayOfMonth1,

ALL('Indemnity Date Dimension'[Indemnity Date].[Date]),

'Indemnity Benefit Statuses'[Benefit Status Description] IN VALUES('Indemnity Benefit Statuses'[Benefit Status Description])

)

RETURN

BenefitDaysSTSSum

[v-agajavelly · Accepted solution]

Hi @Mike_Yukon 

I ran into the same issue where the CumBenefitDaysSelectedBnftSts measure was incorrectly returning cumulative benefit days only for the selected benefit type, even though the goal was to count all benefit days regardless of type, as long as the status, year, and month matched.

After digging into it, the issue turned out to be that the Benefit Type slicer was still influencing the calculation, even though it shouldn’t have.

What worked for me is I rewrote the measure like this.

CumBenefitDaysSelectedBnftSts =
VAR SelectedYear = SELECTEDVALUE('Date Dimension'[Year])
VAR SelectedMonth = SELECTEDVALUE('Date Dimension'[MonthNo])
VAR LastDayOfMonth = EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0)
RETURN
CALCULATE (COUNTROWS('BenefitDays'),'Date Dimension'[Date] <= LastDayOfMonth, 'Benefit Statuses'[Benefit Status Description] IN VALUES('Benefit Statuses'[Benefit Status Description]), REMOVEFILTERS('Benefit Types'))

This gave me the expected result. For example, when I selected only bnftB, the CumBenefitDaysSelectedBnftSts measure still showed cumulative days from both bnftA and bnftB, filtered only by status and date exactly what I needed.

Attaching Screenshot and PBIX for your refferance.

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Reagrds,
Akhil.

[Mike_Yukon]

Hi Akhil. Thanks for the solution. Your suggestion works. Many thanks. Saved me many hours of head-scratching.