Cumulative benefit days

[Mike_Yukon]

Hi everyone. I have a table with benefit schedules (from and through dates) of various benefit types (e.g. A, B, C) with various payment statuses (e.g. paid, pending) for claim numbers (e.g. 1, 2, 3). Each schedule can only have 1 payment status.

Each schedule has been split such that there is one row for every day for every benefit schedule (aka "schedule day").

My report will have slicers to select the year and month of interest, benefit types and payment statuses.

I need a measure to calculate the cumulative number of benefit days for each claim in the report, based on the chosen slicer values.

However, my measure is only returning the number of scheduled days in the selected month -- instead of all days from the beginning of time. For example, the following measure should return 97 days for one claim in particular for 2025 and March (all benefit types, all benefit statuses). Instead, it is returning 31 -- which is the number of benefit days scheduled in that month only.

What am I doing wrong? Any ideas?

CumBenefitDaysSelectedVars =

VAR SelectedYear = SELECTEDVALUE('Indemnity Benefits'[Schedule Day].[Year])

VAR SelectedMonth = SELECTEDVALUE('Indemnity Benefits'[Schedule Day].[MonthNo])

VAR FirstDayOfMonth = DATE(SelectedYear, SelectedMonth, 1)

VAR LastDayOfMonth = EOMONTH(FirstDayOfMonth, 0)

VAR FilteredTable =

FILTER(

'Indemnity Benefits',

'Indemnity Benefits'[Schedule Day].[Date] <= LastDayOfMonth &&

'Indemnity Benefits'[Benefit Type] IN VALUES('Indemnity Benefits'[Benefit Type]) &&

'Indemnity Benefits'[Benefit Status] IN VALUES('Indemnity Benefits'[Benefit Status])

)

VAR BenefitDaysSum =

COUNTX(FilteredTable, 'Indemnity Benefits'[Schedule Day].[Date])

RETURN BenefitDaysSum

[v-kathullac]

Hi @Mike_Yukon ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya

[maruthisp · Accepted solution]

Hi Mike_Yukon,

Your current measure is using the filter context set by your slicers and visuals, so when you select March 2025, the rows in 'Indemnity Benefits' are automatically filtered to only March 2025 dates. Even though your filter in DAX tries to use <= LastDayOfMonth, the context is already restricted to the current month.

please try the below DAX expression:
CumBenefitDaysSelectedVars =
VAR SelectedYear = SELECTEDVALUE('Indemnity Benefits'[Schedule Day].[Year])
VAR SelectedMonth = SELECTEDVALUE('Indemnity Benefits'[Schedule Day].[MonthNo])
VAR LastDayOfMonth = EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0)
VAR FilteredTable =
FILTER(
ALL('Indemnity Benefits'[Schedule Day].[Date]),
'Indemnity Benefits'[Schedule Day].[Date] <= LastDayOfMonth
&& 'Indemnity Benefits'[Benefit Type] IN VALUES('Indemnity Benefits'[Benefit Type])
&& 'Indemnity Benefits'[Benefit Status] IN VALUES('Indemnity Benefits'[Benefit Status])
&& 'Indemnity Benefits'[Claim Number] IN VALUES('Indemnity Benefits'[Claim Number])
)
VAR BenefitDaysSum = COUNTROWS(
FILTER(
'Indemnity Benefits',
'Indemnity Benefits'[Schedule Day].[Date] IN SELECTCOLUMNS(FilteredTable, "Date", 'Indemnity Benefits'[Schedule Day].[Date])
)
)
RETURN
BenefitDaysSum

or
CALCULATE(
COUNTROWS('Indemnity Benefits'),
'Indemnity Benefits'[Schedule Day].[Date] <= LastDayOfMonth,
ALL('Indemnity Benefits'[Schedule Day].[Date]),
'Indemnity Benefits'[Benefit Type] IN VALUES('Indemnity Benefits'[Benefit Type]),
'Indemnity Benefits'[Benefit Status] IN VALUES('Indemnity Benefits'[Benefit Status])
)

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[Mike_Yukon]

Thanks much, Maruthi. Thanks for helping me to understand DAX.

The first solution you proposed does not work. I get the following message: "A single value for column 'Benefit Type' in table 'Indemnity Benefits' cannot be determined . . . "

But, the second soultion appears to work and is simpler code:

CumBenefitDaysSelectedVars =

/* solution proposed by maruthisp */

VAR SelectedYear = SELECTEDVALUE('Indemnity Benefits'[Schedule Day].[Year])

VAR SelectedMonth = SELECTEDVALUE('Indemnity Benefits'[Schedule Day].[MonthNo])

VAR LastDayOfMonth = EOMONTH(DATE(SelectedYear, SelectedMonth, 1), 0)

VAR BenefitDaysSum =

CALCULATE(

COUNTROWS('Indemnity Benefits'),

'Indemnity Benefits'[Schedule Day].[Date] <= LastDayOfMonth,

ALL('Indemnity Benefits'[Schedule Day].[Date]),

'Indemnity Benefits'[Benefit Type] IN VALUES('Indemnity Benefits'[Benefit Type]),

'Indemnity Benefits'[Benefit Status] IN VALUES('Indemnity Benefits'[Benefit Status])

)

RETURN

BenefitDaysSum

Many thanks! You have saved me hours of hair pulling and head scratching!