Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
NewbieJono
Post Patron
Post Patron

show pat 7 days from selected date

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

12 REPLIES 12
v-chenwuz-msft
Community Support
Community Support

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

vchenwuzmsft_0-1634197391383.png

 

The result :

vchenwuzmsft_1-1634197391387.png

 

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
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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

OK. Here is the model

Model.JPGThen 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:

Result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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?

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






date

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:

Synch1.JPG

 

Synch2.JPG

 

Synch.gif

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

Interact.JPG

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

Hide.gif

 

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

Result.gif

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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. 

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
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.