Forum Discussion

disney_batista's avatar
disney_batista
Frequent Visitor
1 year ago

Datepicker filter

I need a date filter that allows me to assign predefined buttons for periods such as: last month, year-to-date, total period, considering the date reference available in the database and not in the current calendar. In addition, the dates must update dynamically as new dates are inserted into the database. So, by default the selection is "last month" where the last date loaded is September 2024, when loading data from October 2024 into the database the selector will automatically update the last month in the selection to October 2024.
Do you know of any way to put this into practice? I used the Powerviz datepicker but it does not meet my needs with the default date

Tips and Tricks

5 Replies

  • tomorrowyw's avatar
    tomorrowyw
    New Member
    1 year ago

    Hi, you can create a measure to get the latest date in your data:

    1.

    Latest Date = MAX('YourTable'[DateColumn])

    2. Create measures for different date ranges:

    Last Month Start = EOMONTH([Latest Date], -2) + 1 Last Month End = EOMONTH([Latest Date], -1) Year To Date Start = DATE(YEAR([Latest Date]), 1, 1) Year To Date End = [Latest Date] Total Period Start = MIN('YourTable'[DateColumn]) Total Period End = [Latest Date]

    3.Create a parameter for date range selection:

    In Power BI Desktop, go to Home > Enter Data and create a new table:

    Date Range

    Last Month

    Year to Date

    Total Period

     

    1. Create a measure for dynamic date filteringDynamic Date Filter = VAR SelectedRange = SELECTEDVALUE(DateRangeSelector[Date Range], "Last Month") RETURN SWITCH( SelectedRange, "Last Month", 'YourTable'[DateColumn] >= [Last Month Start] && 'YourTable'[DateColumn] <= [Last Month End], "Year to Date", 'YourTable'[DateColumn] >= [Year To Date Start] && 'YourTable'[DateColumn] <= [Year To Date End], "Total Period", 'YourTable'[DateColumn] >= [Total Period Start] && 'YourTable'[DateColumn] <= [Total Period End], TRUE )

    4

    1. Use the filter in your visuals:
      Add the "Dynamic Date Filter" measure to the filter pane of your visuals and set it to "is TRUE".
    2. Add a slicer:
      Add a slicer to your report using the DateRangeSelector table

     

     

     

    • disney_batista's avatar
      disney_batista
      Frequent Visitor
      1 year ago

      Hey tomorrowyw,

      Thanks for the suggestions, I'm trying to implement the suggestion you sent me, but I can't replace the code snippets that mention "YourTable'[DateColumn]" with my date column.

      My date column is called 'D_Calendar'[Date].

      I get the following return: "Cannot determine a single value for column ''Date'' in table ''D_Calendar''. This can happen when a measure formula refers to a column that contains many values ​​without specifying an aggregation such as min, max, count or sum to obtain a single result."