Manipulate date slicer behaviour with Graph visual

[AshishTanwar88]

Hi All,

Currently, In the date slicer, we are choosing the last three days then the date range showing as "12/14/2024 to 12/16/2024" which is correct. And if there is no data available for 12/16/2024 (today)
graph visual shows only data for the 14th & 15th which is the correct graph behavior.

But we need, if there is no data available for 12/16/2024 (today)
or N number of days from today then the graph visual should show the last three days from the last available data date.

For example, if the last data availability date is 15/12/2024 then selecting "Last 3 days" (today is 16/12/204) in the date slicer, the graph should show the data for 15th to 13th Dec instead of 16th to 14th. The slicer should also show the selected date range "12/13/2024 to 12/15/2024" based on the last availability date of data. Is there any direct way of doing this?

Any help or guidance is much appreciated. 

Thank you.

[v-jiewu-msft · Accepted solution]

Hi @AshishTanwar88 ,

Based on the testing, try using the following DAX formula.

Is not today = 
IF(MAX('Table'[Date]) = TODAY() && MAX('Table'[Date]) > TODAY() - 3, 1,
    IF(MAX('Table'[Date]) >= TODAY() - 3 && MAX('Table'[Date]) < TODAY(), 1, 0)
)

Then, drag the measure to the table visual filters pane and set the show item is 1.

Besides, set the measure for the slicer visual.

You can also view the following link to learn more information.

Solved: Required custom date Slicer Last 7 days,last 15 da... - Microsoft Fabric Community

Best Regards,

Wisdom Wu

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

[lbendlin]

Filter your calendar table by the TOPN(3,..) of the data dates, using TREATAS or calculated columns if you do daily refreshes.

[DataNinja777]

Hi @AshishTanwar88 ,

To achieve the desired behavior in Power BI, where the date slicer dynamically adjusts based on the last available data date, you can use a combination of DAX measures and a calculated table. Start by creating a DAX measure to identify the last date with data available. This can be done using the formula:

LastAvailableDate = 
CALCULATE(
    MAX('YourTable'[Date]),
    NOT(ISBLANK('YourTable'[Value]))
)

Here, replace [Value] with the column that determines whether data exists for a given date. This measure finds the maximum date in your dataset where data is available.

Next, create a calculated table that generates a dynamic three-day date range ending on the last available data date. Use the following formula:

DynamicDateRange = 
ADDCOLUMNS(
    CALENDAR(
        DATEADD([LastAvailableDate], -2, DAY), 
        [LastAvailableDate]
    ),
    "DisplayDate", FORMAT([Date], "MM/DD/YYYY")
)

This table ensures the three-day window adjusts dynamically based on the last available date. Then, link the DynamicDateRange table to your main table using the Date column, and use the DynamicDateRange[Date] as the slicer field in your report.

To ensure the slicer dynamically displays the correct date range, you can modify the visual title or slicer label to reflect the range. Create a DAX measure like this:

SelectedDateRange = 
VAR StartDate = MIN(DynamicDateRange[Date])
VAR EndDate = MAX(DynamicDateRange[Date])
RETURN
"Selected Range: " & FORMAT(StartDate, "MM/DD/YYYY") & " to " & FORMAT(EndDate, "MM/DD/YYYY")

This measure dynamically updates the displayed range based on the adjusted slicer selection. By using this setup, your slicer and graph will adjust to show data for the last three days based on the most recent available date, ensuring accurate and dynamic visuals even when the latest data is unavailable.

Best regards,