I apologize right off as I could not find a way to upload Excel workbooks.
I have been asked to create a report that details the last scheduled benefit in a given month (year and month slicers) for a given benefit type (another slicer, where up to 8 different benefit types can be chosen) and benefit statuses (another slider, where up to 5 different benefit statuses can be choses, e.g. paid, pending, etc).
Schedules for benefits are non-overlapping (except in rare cases). In other words, one schedule for January 28-February 2 will not overlap with any other schedule. Other schedules will either precede those dates or come after those dates. In the small number of cases where there are overlapping schedules, we can live with a bit of duplication.
The inbound table has schedules for each claim and benefit type and benefit period (from and through date). Each schedule can only have one current benefit status (i.e. cannot both be paid and pending).
Each of these schedules has been split in the inbound table into N rows, with one row representing each day in the benefit schedule ("benefit day"). Example: claim # 1234 has a schedule for benefit type A for the following from and thru dates: January 28 - February 02. There will be one row for each day in the schedule: January 28, January 29, January 30, January 31, February 1, February 2.
This is to facilitate the summing of benefit days by month, etc, since benefit schedules may overlap months (as shown above).
I have a date dimension that links to each of the benefit days mentioned above. The date table has an end-of-month date variable, so I always know which schedules fall into a given month/year. So, in the example above, the end-of-month variable for Janaury 28 is January 31. The end-of-month variable for February 1 is February 28, etc.
Ideally, the user should also have a slicer for "benefit duration category": "90 days or less", "91 days to 180 days", "181 to 365 days", 366 days or more". This slicer will be based on the (distinct) cumulative days for the chosen benefit types and statuses to the end of the selected month. Essentially, this slicer depends on the output of the other slicer values. If computing this measure is too complex, we can resort to a simple descending sort of one of the output report’s relevant cumulative days column.
Simplified sample of inbound data (only 1 claim shown for convenience. Fictional benefit schedules.):
Claim_no | benefit_type | benefit_day | lastdayinmonth
(from date dimension) | benefit_status | benefit_schedule_str |
1234 | A | 28-Jan-25 | 31-Jan-25 | paid | A, 2025-01-28 to 2025-02-02, paid |
1234 | A | 29-Jan-25 | 31-Jan-25 | paid | A, 2025-01-28 to 2025-02-02, paid |
1234 | A | 30-Jan-25 | 31-Jan-25 | paid | A, 2025-01-28 to 2025-02-02, paid |
1234 | A | 31-Jan-25 | 31-Jan-25 | paid | A, 2025-01-28 to 2025-02-02, paid |
1234 | A | 01-Feb-25 | 28-Feb-25 | paid | A, 2025-01-28 to 2025-02-02, paid |
1234 | A | 02-Feb-25 | 28-Feb-25 | paid | A, 2025-01-28 to 2025-02-02, paid |
1234 | B | 03-Feb-25 | 28-Feb-25 | rel | B, 2025-02-03 to 2025-02-06, rel |
1234 | B | 04-Feb-25 | 28-Feb-25 | rel | B, 2025-02-03 to 2025-02-06, rel |
1234 | B | 05-Feb-25 | 28-Feb-25 | rel | B, 2025-02-03 to 2025-02-06, rel |
1234 | B | 06-Feb-25 | 28-Feb-25 | rel | B, 2025-02-03 to 2025-02-06, rel |
1234 | A | 07-Feb-25 | 28-Feb-25 | pendauth | A, 2025-02-07 to 2025-02-07, pendauth |
1234 | C | 01-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 02-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 03-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 04-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 05-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 06-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 07-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 08-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 09-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 10-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 11-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 12-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 13-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
1234 | C | 14-Dec-93 | 31-Dec-93 | paid | C, 1993-12-01 to 1993-12-14, paid |
The intent is to allow the user to select one or more benefit types, one or more benefit statuses, the duration category, and the year and month. The report will essentially show:
- claim #
- the date representing the end of the month selected (for visual convenience for the user)
- a brief description of the last scheduled benefit in the month (this is a pre-generated text string generated from the original schedule and is on every row of the inbound table -- but I need a way to return only the last one for the selected slicer values for each claim
- the distinct number of benefit days in the month (based on the slicer values)
- the cumulative distinct number of benefit days from the beginning of time to the end of the chosen month (based on the slicer selections)
- the cumulative distinct number of benefit days for ALL benefit types (regardless of benefit type) to the end of the selected month but dependent on the benefit status(es) set by the user in the slicer
- the benefit duration category per # 5
If user chooses the following slicers:
year = 2025
month = February
benefit type = A, B
benefit status = paid, rel
benefit duration category = 0 to 90 days
Output should be as follows;
Claim_no | lastdayinmonth | benefit_schedule_str | cum_selctd_bnft_days_mnth | cum_selctd_bnft_days | cum_bnft_days | duration_category |
1234 | 28-Feb-25 | B, 2025-02-03 to 2025-02-06, rel | 6 | 10 | 24 | 0 to 90 days |
If user chooses the following slicers:
year = 1993
month = December
benefit type = A, B, C
benefit status = paid, pend, pend auth, rel
benefit duration category = 0 to 90 days
Output should be:
Claim_no | lastdayinmonth | benefit_schedule_str | cum_selctd_bnft_days_mnth | cum_selctd_bnft_days | cum_bnft_days | duration_category |
1234 | 31-Dec-93 | C, 1993-12-01 to 1993-12-14, paid | 14 | 14 | 14 | 0 to 90 days |
In summary, I need a way to:
- Select the last schedule for the month (based on slicer values) for the claim
- Compute the cumulative (distinct) benefit days in the month (based on the slicer values)
- Compute the cumulative (distinct) benefit days for all selected schedules in history to the end of the selected month (based on slicer values)
- Compute the cumulative (distinct) benefit days for all schedules in history to the end of the selected month (based on payment statuses and month only)
- The benefit duration category (based on compute statement in #3)
