Forum Discussion
Custom column to filter a date table dynamically
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.
This worked perfectly. It was causing a lot of stress! Thanks very much for helping.
3 Replies
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 () )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.
This worked perfectly. It was causing a lot of stress! Thanks very much for helping.
