Creating a custom Date Slicer

Question

Hello,&nbsp;I am trying to create a parameter/variable/formula to return a given period of time.&nbsp; I have three periods.&nbsp;I want the user to be able to select a Marketing Campaign Name and the data switches based on the campaign slicer to the dates below.&nbsp;Marketing campaignPenetration: 1/1/18 to 5/25/2018BOGO: 5/26/18 to 7/4/18Contracted Deals: 7/5/18 - 10/15/18&nbsp;I started by making a table with the names of the campaigns.&nbsp;Marketing CampaignIndexPenetration1BOGO2Contracted Deals3&nbsp;I included an index, and sorted by the index, so when I add the table values as a card, they appear in the desired order.&nbsp;After that I don't know how to set up my variables or create measures to use in the switch formula.&nbsp; I don't think I need parameters in this case, but I don't know how to set the date range either way.&nbsp; I'm less than 40 hours into building DAX.&nbsp;After I have my variables I think I can use Switch(True(),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[Campaign Selected] = "Penetration", [Penetration] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[Campaign Selected] = "BOGO", [BOGO] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[Campaign Selected] = "Contracted Deals", [Contracted Deals] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;[????] )&nbsp;But I'm going to get caught up again because I don't how to create a time period for the false period which would be 1/1/18 - 10/15/18. I guess another option would be to have four periods with one being the full period.&nbsp;Appreciate the help!&nbsp;&nbsp;

lbendlin · Answer

For your campaigns add two more columns to the reference table. Start Date and End Date.

Then when a campaign is selected you can filter your dates table using DATESBETWEEN() with the selected campaign's dates.

This will then automatically filter your transaction table for that date range.

Note: If you only compare against a single value you can simplify the switch statement like this:

Switch([Campaign Selected],"Penetration", [Penetration],"BOGO", [BOGO],"Contracted Deals", [Contracted Deals] ,or else)

allisonkennedy · Answer

What is the formula for [Contracted Deals]?I assume it includes the date range, so build something similar with the false condition.

v-alq-msft · Answer

Hi,&nbsp;Anonymous&nbsp;
&nbsp;
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
&nbsp;
Table:

&nbsp;
Test:

&nbsp;
Calendar(a calculated table):
Calendar = CALENDAR(DATE(2015,1,1),DATE(2020,12,31))
&nbsp;
There is no relationship between tables. You may create two measures as below.
DateRange = 
var _cam = SELECTEDVALUE('Table'[Marketing Campaign])
return
IF(
    ISFILTERED(Test[Marketing Campaign]),
    SWITCH(
        TRUE(),
        "Penetration" in DISTINCT(Test[Marketing Campaign])&amp;&amp;_cam = "Penetration",
        "1/1/18 to 5/25/2018",
        "BOGO" in DISTINCT(Test[Marketing Campaign])&amp;&amp;_cam = "BOGO",
        "5/26/18 to 7/4/18",
        "Contracted Deals" in DISTINCT(Test[Marketing Campaign])&amp;&amp;_cam = "Contracted Deals",
        "7/5/18 to 10/15/18",
        BLANK()
    )
)

Visual Control = 
var _date = SELECTEDVALUE('Calendar'[Date])
return
IF(
    ISFILTERED(Test[Marketing Campaign]),
    IF(
        HASONEVALUE(Test[Marketing Campaign]),
        SWITCH(
            SELECTEDVALUE(Test[Marketing Campaign]),
            "Penetration",
            IF(
                _date&gt;=DATE(2018,1,1)&amp;&amp;
                _date&lt;=DATE(2018,5,25),
                1,0
            ),
            "BOGO",
            IF(
                _date&gt;=DATE(2018,5,26)&amp;&amp;
                _date&lt;=DATE(2018,7,4),
                1,0
            ),
            "Contracted Deals",
            IF(
                _date&gt;=DATE(2018,7,5)&amp;&amp;
                _date&lt;=DATE(2018,10,15),
                1,0
            )
        ),
        0
    ),
    0
)
&nbsp;
Finally you need to put 'Visual Control' measure in the corresponding visual level filter to filter out the result.

&nbsp;

&nbsp;
Best Regards
Allan
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
&nbsp;

anonymous · Answer

Thanks for the help.&nbsp;I couldn't figure out how to write the datesbetween formula, and I actually have data that goes into dynamic dates.&nbsp; I couldn't figure out how to write into a table MAX(Date) or TODAY(), so I couldn't take this approach.&nbsp;Appreciate the help though!

anonymous · Answer

Thank you for the help.&nbsp;I couldn't get it to work until I figured out I needed to create a new table, and write it as a formula.&nbsp;The solution worked once I figured that part out.&nbsp; I had to add in some dynamic dates and did that by adjusting the date(2020,5,1) to MAX(DATE) in the two DAX formulas.&nbsp;However when I try to deselect the certain period to see the overall time frame, it doesn't work.&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Creating a custom Date Slicer

6 Replies

Related content

Custom Date Slicers

How creating a Python custom library

Creating custom slicer

Creating date slicer removes data

Creating a dynamic slicer

Marketing Campaign	Index
Penetration	1
BOGO	2
Contracted Deals	3