Search code examples
powerbidatefilter

Power BI: What-If parameter with a date filter

I need to display data on a table that will display data from six days ago based on the date selected on the filter. For example, if the date selected on the filter is "2020/06/30", I need the table to display data from "2020/06/29", "2020/06/28", "2020/06/27", "2020/06/26", "2020/06/25", "2020/06/24".

I tried to use a What-If parameter, but I can not link the parameter to the date column on my table. Please help. Thank you.

Solution

  • For your purpose, You basically need a disconnected Calendar table. You can create a separate custom table for all dates as below-

    Considering your calendar table name Dates

    Considering your fact table name your_fact_table_name

    Dates_disconnected =
SELECTCOLUMNS(
    Dates,
    "Date",Dates[Date]
)

    Now, create your Date sclicer using the new custom table Dates_disconnected.

    Finally, create a Measure as below in your Base table-

    filter = 

VAR current_row_date = MIN(your_fact_table_name[date])
VAR selected_date = SELECTEDVALUE(Dates_disconnected[Date])
VAR selected_start_date = SELECTEDVALUE(Dates_disconnected[Date]) - 5

RETURN 
IF(
    current_row_date < selected_date && current_row_date>= selected_start_date,
    "No",
    "Yes"
)

    considered last 5 day in the above measure. you can adjust the range.

    The above Measure will give you some output as below-

    enter image description here

    You can see Previous 5 day (considering selected date in slicer) is showing No and all other row is showing Yes. Now, just apply a visual level filter using the measure filter and filter out all rows having Yes in the row.