Custom column to filter a date table dynamically

[STEVE_WT]

Hi

I have a list of campaign which a user will choose from a drop down slicer. Each of these campaigns has a start and end date. 

I also have a standalone calendar table with a list of dates. I need this to be filtered based on what the start and end date are of the the chose campaign.

This standalone date tabel will then be attached a table of transactions. 

See below.

Campaign dropdown is in the top left and the start and end date for that below. These will change with each campaign chosen.

The date list is on the right.

I need help with how to create a measure to dynamically filter the date list.

[Anonymous]

Hi @STEVE_WT ,

You can create a measure.

FilteredDates =
VAR SelectedCampaign = SELECTEDVALUE(Campaign[CampaignName])
VAR StartDate = CALCULATE(MIN(Campaign[StartDate]), Campaign[CampaignName] = SelectedCampaign)
VAR EndDate = CALCULATE(MAX(Campaign[EndDate]), Campaign[CampaignName] = SelectedCampaign)
RETURN
IF(MAX(Calendar[Date]) >= StartDate && MAX(Calendar[Date]) <= EndDate,1)

Then drag it to the date table visual filter to filter data with a value of 1.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[STEVE_WT · Accepted solution]

This worked perfectly. It was causing a lot of stress! Thanks very much for helping.

[johnt75]

I'd create a calculation group with a calculation item like

Calc item =
IF (
    HASONEVALUE ( 'Table'[campaign name] ),
    CALCULATE (
        SELECTEDMEASURE (),
        DATESBETWEEN (
            'Date'[Date],
            SELECTEDVALUE ( 'Table'[start date] ),
            SELECTEDVALUE ( 'Table'[end date] )
        )
    ),
    SELECTEDMEASURE ()
)