show pat 7 days from selected date

[NewbieJono]

i have a date slicer with single selection, is there a method to return past 7 days from the date selected in the slicer?

[v-chenwuz-msft]

Hi @NewbieJono 

You can create a table without any relationships with other tables and just one date column used to be base data for slicer.

I recommend the following dax

slicer = VALUES('CALENDAR'[Date])

And create a measure

Measure =
VAR _Sdate =
    SELECTEDVALUE( slicer[slicer_Date] )
VAR _first = _Sdate - 7
VAR _last = _Sdate - 1
RETURN
    IF(
        SELECTEDVALUE( 'CALENDAR'[Date] ) >= _first
            && SELECTEDVALUE( 'CALENDAR'[Date] ) <= _last,
        1,
        BLANK()
    )

Then drag measure to the visual and set it show items is 1

The result :

I put my pbix file in the end you can reference.

Best Regards

Community Support Team _ chenwu zhu

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

[PaulDBrown]

You will need to use an independent (unrelated) date table as the slicer. You can create this table with new table under "Modeling" in the ribbon and

Ref table = 'Date table' 

You can the create a measure along the lines of:

Filter date rows = IF(MAX(Date[Date]) >= SELECTEDVALUE (Ref date [Date])-6 && MAX(Date[Date]) <= SELECTEDVALUE (Ref date[Date]), 1)

create your visual using the Date table date, and add the [Filter date rows] measure to the filters on the visual in the Filter Pane and set the value to 1

[NewbieJono]

Sorry can you explain this a bit more, would i duplciate my current date table?

[PaulDBrown]

Yes. You need a new table which is unrelated to the model. If you only need the date field, you can create the new table using

Ref table = DISTINCT(Date table [Date])

This is the table you use as the slicer, and then follow the instructions in my previous post

[NewbieJono]

it dont seem to work for me.

Filter date rows = IF(MAX('DIM - Date Table'[Date]) >= SELECTEDVALUE ('Ref table'[Date])-6 && MAX('DIM - Date Table'[Date]) <= SELECTEDVALUE ('Ref table'[Date]), 1)

i add to the filter plane but the resilts show nothing

[PaulDBrown]

OK. Here is the model

Then create the measure for the filter

Filter Rows =
IF (
    MAX ( 'Date Table'[Date] )
        >= SELECTEDVALUE ( 'Ref table'[Date] ) - 6
        && MAX ( 'Date Table'[Date] ) <= SELECTEDVALUE ( 'Ref table'[Date] ),
    1
)

Cretae the visual with the date field from the regular Date Table and the measures. Add the [Filter Rows] measure to the filter for the visual in the filter pane and you get:

[NewbieJono]

i have a slicer from my date table to control the other visuals? if i use ref date as a slicer instrad the other visuals will stop working?

[PaulDBrown]

Is the date table slicer based on the date? Month? YearMonth?

[NewbieJono]

date

[PaulDBrown]

Sure, you can use it. Here's how

Include the Date slicer and other visuals. Next select the Ref Date slicer and type in a name under View / Synch Slicers / Advanced Options type in a name (I've typed "Select date"). Do the same with the date slicer.  The slicers will then be synched:

 Next turn off the interactions between the Date Slicer and the visual showing the values for the last 7 days:

Finally, select the Ref date slicer und hide the slicer under View / Selection 

And you can now use the Date slicer to filter all the visuals

[NewbieJono]

thank you so much for this help. it works perfect. 

if i change the date drop down to a slider filter. how could i have it shouw the dates in between max and min date on the slicer. 

[PaulDBrown]

Try the following as the filter measure:

Filter selected Rows =
VAR MinD = MIN(Ref table [Date])
VAR MaxD = MAX(Ref table [Date])
RETURN
IF (
    MAX ( 'Date Table'[Date] )
        >= MinD
        && MAX ( 'Date Table'[Date] ) <= MaxD,
    1
)