Gantt Chart Date slicer

[CarlsBerg999]

Hi,

I have an issue with creating an effective slicer to a gantt chart. The issue is with the dates, each task has a start and end date. What i want to have on a slicer is "Current Month, Next Month, Next Quarter, Current Year". For example:

Next Month should include all tasks that have an end date next month OR has a start date next month. 

Next Quarter  should include all tasks that have an end date next quarter OR has a start date next quarter.

How would you perform this? Note that Gantt or slicer visual doesn't accept measures. Therefore i think we need to work with calculated columns.

[Anonymous · Accepted solution]

Hi @CarlsBerg999 ,

I'd suggest you create a flag measure based on the different date period.

Please follow these steps:

1. Create a separate table for slicer via Enter data:

2. Create a flag measure:

Flag =
VAR _start =
    MONTH ( MAX ( 'Data'[Start Date] ) )
VAR _end =
    MONTH ( MAX ( 'Data'[End Date] ) )
VAR _today =
    MONTH ( TODAY () )
RETURN
    SWITCH (
        MAX ( 'ForSlicer'[Period Type] ),
        "This Month",
            IF ( _start <= _today && _end >= _today, 1 ),
        "Next Month",
            IF ( _start <= _today + 1 && _end >= _today + 1, 1 ),
        "Next Quarter",
            IF (
                QUARTER ( MAX ( 'Data'[Start Date] ) )
                    <= QUARTER ( TODAY () ) + 1
                    && QUARTER ( MAX ( 'Data'[End Date] ) )
                        >= QUARTER ( TODAY () ) + 1,
                1
            ),
        "This Year",
            IF (
                YEAR ( MAX ( 'Data'[Start Date] ) ) <= YEAR ( TODAY () )
                    && YEAR ( MAX ( 'Data'[End Date] ) ) >= YEAR ( TODAY () ),
                1
            )
    )

3. Apply it to filter pane, set as "is 1" , the final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@CarlsBerg999 
after creating dummy table with above mentioned way, you need to create the measure with selectedvalue() which gives control to access the data based on the slicer selection ,

then you can use IF(selectedvalue(nextmonth), what you require,

if(selectedvalue(nextqtr),what you require

is the final measure

[VijayP]

@CarlsBerg999 

Create a dumymy TAble with above variables in a column ( as row values)

i.e., "Current Month........"

with Selectedvalue function invoke powerbi to know what you have selected 

and based on that measure you can get final result

[CarlsBerg999]

Hi, 

Could you elaborate on this? It's not possible to get these all on one column. For example, next quarter includes next month. Therefore, i would need multiple columns. If i have multiple columns, its not possible to create a single slicer for the selected value to pick up upon. I assume that in this idea, i would put selectedvalue as a filter on the visual