Using a Value from a Date Slicer to Populate the Year of the StartDate in Between-style Date Slicer

[RunningBlue]

I'm trying to add a column in my table visual to count the # of cases closed since April 1 of a specified year until a specified end date. I've tentatively got a measure that does this, so it's just a matter of now making the slicers/UI elements for those two selections. However, I already have the Date Closed field filtering the table visual for '# of cases' and other measures, so Date Closed cannot be reused as a slicer without affecting the rest of the visual. My approach was to use a disconnected table for a slicer to first select the year, and then use that chosen value to make a calendar table to be used as the slicer that actually filters '# of cases closed since April 1' in the visual. However, the chosen year doesn't seem to be working (the default 1900 populates the table instead). See the following pic for an illustrated explanation of what I mean.

Here's the table for the Slicer for the StartDate:

Calendar Since April StartDate = CALENDAR(
    DATE(
        YEAR(MIN(PBI_Secondary[Date Closed])) - 1, 
        1, 
        1),
    (DATE(
        YEAR(MAX(PBI_Secondary[Date Closed])) + 1, 
        12, 
        31)
        )

Here's the Between-style Slicer:

Calendar Since April BetweenX = CALENDAR(
    DATE(SELECTEDVALUE('Calendar Since April StartDate'[Year]), 4, 1),
    DATE(
        YEAR(MAX(PBI_Secondary[Date Closed])) + 1, 
        12, 
        31)
    )

And for completion's sake, here's the DAX for the measure that populates the # of Cases Since April column in the visual:

Cases Since April = 
VAR StartDate = MIN('Calendar Since April BetweenX'[Date])
VAR EndDate = MAX('Calendar Since April BetweenX'[Date])
VAR Cases = 
CALCULATE(
    DISTINCTCOUNT('PBI_Secondary'[Case ID]),
    PBI_Secondary[Date Closed] >= StartDate,
    PBI_Secondary[Date Closed] <= EndDate,
    REMOVEFILTERS(PBI_Secondary[Date Closed].[Year], PBI_Secondary[Date Closed].[Month])
)
RETURN
IF(ISBLANK(Cases), 0, Cases)

I'm relatively new to Power BI, so if there's an obvious approach I'm missing that gets what I'm aiming for, I do apologize.

Appreciate any help or guidance!

[DataInsights]

@RunningBlue,

Assuming your model has a date table 'Date', you could try a measure like this. The date table should have a relationship with the fact table (PBI_Secondary). The YearSlicer table is a disconnected table (no relationships) consisting of the years to include in the slicer. The Between date slicer doesn't reflect the selected year because calculated tables and calculated columns aren't able to recognize user selections.

Cases Since April =
VAR vSelectedDate =
    DATE ( SELECTEDVALUE ( YearSlicer[Year] ), 4, 1 )
VAR vResult =
    CALCULATE (
        DISTINCTCOUNT ( 'PBI_Secondary'[Case ID] ),
        'Date'[Date] >= vSelectedDate
    )
RETURN
    vResult

[RunningBlue]

Oops, I had a typo in my post - fixed it now. The "Cases Since April" DAX snippet I wrote is for the measure that populates the # of Cases Since April column; it's not for a calculated column as I had mistakenly originally wrote. The measure I wrote works fine in retrieving the Between slicer's values, so if the user specifies the StartDate manually as April 1 of whichever specified year (and the specified EndDate), everything is calculated as expected.

However, the YearSlicer isn't restricting the Between slicer's StartDate, which you note is not possible. In that case, are there any other alternatives?

Sorry if I wasn't clear before about "Cases Since April", it should be more "Cases Since April of Specified Year until Specified End". I.e., the user should be able to select a specified year, and then select a specified end date (which can have the same or a later year than the earlier specified year).

[DataInsights]

@RunningBlue,

Are the Year slicer and Between date slicer based on the same table?

[RunningBlue]

No, the slicers at the moment are two disconnected tables.

The reason for that approach was because my visual is being filtered by a Date Closed table for various measures like "# of Cases", but the measure in question, "# of Cases Since April",  is also from a business viewpoint supposed to be filterable based on Date Closed. However, from my understanding and from what I've tried, the slicer based on that one field ends up affecting each measure when they're all in one visual so I can't just use the single slicer which is what led to me trying disconnected tables.

I.e., with a slicer based on Date Closed, I would be filtering "# of Cases", "# of Cases Completed", etc. in which I pick a specific month , say July.

However, for the "# of Cases Since April" measure, I would be specifying a Year of that April, say 2023, and then an End Date, say December 12, 2023.

[DataInsights]

@RunningBlue,

Thanks for the clarification. You might consider making the Between slicer a Before slicer, so you can capture the user-specified end date from the Before slicer. Then, calculate the start date using the Year slicer (e.g., 2024 becomes 2024-04-01). Using these start and end dates, you can control the filter in the measure via DAX.