Using single date slicer to filter three visuals by different ranges

[wayne_taylor]

Hi All

I've been wrestling with this one for a few days now, hopefully someone can help me!

I've got a Date Slicer set to select a single date in a dropdown and I am applying this to a visual which works fine.

I then also have 2 other identical visuals that I want to filter on a range that goes from the selected date minus 7 days for one visual and minus 21 days for the other one.

I've tried creating calculated fields to use in the visual filter, but have a context issue so it always is based on the max value of the slicer date field and not the selected one!

Any ideas??

TIA
Wayne

[ak2710]

Hello,

I have the similar kind of a problem, I have two different visuals, 1 visual needs to show the data for date range selected in the slicer 2nd one should show the date range and in addition last 7 days of data if exists.

The problem I'm facing with the above solution is that I have a category in the x axis and not the date and that is resulting in in appropriate results.. can some one help me on this....

[MFelix]

Hi @ak2710 ,

This is possible and using a similar logic to the previous ones should work properly, however you need to have a selection of the dates you want to use in order to have the context for the last 7 days, so you need to have both the date fields select so you can have it has needed.

In this case I created the following code:

7 Days Date Visual = 
CALCULATE (
    SUM ( 'Sales Order Detail'[LineTotal] ),
    FILTER (
        ALL ( 'Calendar'[Dates] ),
            'Calendar'[Dates]<= MAX ( FilterCalendar[Dates] )
            && 'Calendar'[Dates]
                >= MAX ( FilterCalendar[Dates] ) - 7
    )
)

Has you can see I have a slicer with the filter dates and on the category you can see there is a value that refers only to the last 7 days:

[ak2710]

Thanks @MFelix  for the quick reply, Calender[Dates] is my DimDate Table correct? & wht about the FilterCalendar[Dates] is it something the disconnected date table from where the date slicer is coming or it is coming from my fact table?

[MFelix]

Hi, 

The calendar is the dimtable the filltercalendar is a disconnected table that I'm only using to filter the date for the 7 days calculation. 

[MFelix · Accepted solution]

Hi @wayne_taylor ,

When making the use of slicer you are filtering the information in your visual so when you have more than one visual that you need to have different periods you need to have a disconnected date for the slicer then you need to create some measure similar to this ones:

Single Date Visual =
CALCULATE (
    SUM ( Table[Column] );
    FILTER (
        ALL ( Table[Column Date] );
        Table[Column Date] = MAX ( FilterTable[Column Date] )
    )
)


7 Days Date Visual =
CALCULATE (
    SUM ( Table[Column] );
    FILTER (
        ALL ( Table[Column Date] );
        Table[Column Date] <= MAX ( FilterTable[Column Date] )
            && Table[Column Date]
                >= MAX ( FilterTable[Column Date] ) - 7
    )
)

21 Days Date Visual =
CALCULATE (
    SUM ( Table[Column] );
    FILTER (
        ALL ( Table[Column Date] );
        Table[Column Date] <= MAX ( FilterTable[Column Date] )
            && Table[Column Date]
                >= MAX ( FilterTable[Column Date] ) - 21
    )
)

Then use the Date of the mains table of the dates as X-axis on your visual and the measures on each of the visual should work as expected.

See attach file.

Regards,

MFelix