Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Dynamic date slicer

Hello! First time poster: I am trying to make a date slicer, but I want the date to be dynamic, depending on another piece of information (earliest villain date): I want to have a graph o...
  • AllisonKennedy's avatar
    1 year ago

    Okay, see attached file for a solution: 

     

     

    Add a Custom Column to your data table using Power Query > Add Column > Custom Column

    Duration.Days( [Run in with Cops] - [Earliest Villain Date])

     

    Then create a new measure: 

    Run in is within Selected Time Period = 
    VAR _DaysWithin = SELECTEDVALUE( 'Time Slicer'[Days] )
    VAR _Result = COUNTROWS( FILTER( 'Table', ABS('Table'[Days Since Earliest Villain Date]) <= _DaysWithin) )
    RETURN
    IF( MAX( 'Table'[Days Since Earliest Villain Date] ) = BLANK(), BLANK(), _Result )

     

     

     

     

    I also added a date table for good practice, but you don't need it yet here: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html