Default filter to show data from the last 6 months but also allowing the user to modify the filter

[julsr]

Hi everyone,

I have one table that contains values such as Date|Name|SaleAmount, and I want to set the report to enable it to always, by default, load all visualizations and filters considering six months before the current date. So, if today is 01 July 2024, it should show data from 01 Jan 2024 to 01 July 2024. However, the user must be able to modify it to see data of another range (i.e., data from previous years). How could I do this? I know that Relative Dates could be used, but it doesn't allow me to configure the report to enable the user to filter as he wants.

Thanks

[alinazarovf]

Hi,

I would suggest creating 2 measures. First, the sales amount, which would always show the last 6 months by default for your report. And second sales amount measure with custom (user-defined) dates to compare with the first measure.

Part 1. Measure with default filter showing last 6 months sales

1.1 First you need 1 extra measure to avoid showing results in future dates.

ShowValueForDates =

VAR LastDateWithData =

    CALCULATE (

        MAX ( 'Sales'[Date] ),

        REMOVEFILTERS ()

    )

VAR FirstDateVisible =

    MIN ( 'Date'[Date] )

VAR Result =

    FirstDateVisible <= LastDateWithData

RETURN

    Result

1.2 Create measure for last 6 months data using the following code:

Sales amount (last 6 months) =

IF (

    [ShowValueForDates],

    CALCULATE (

        [your aggregation measure (for example sales amount)],

        DATESINPERIOD (

            'Date'[Date],

            MAX ( 'Date'[Date] ),

            -6,

            MONTH

        )

    )

)

2. Custom measure for user-defined period.

2.1 Create one more date table, name it for example comparison dates (it should contain same dates as your main date table). Then connect newly created date table to your main date table, relationship should be inactive, one (comparison date table) -> to -> many (main date table).

2.2. Create your 2nd measure by using comparison dates:

Comparison Sales Amount =

VAR ComparisonPeriod =

    CALCULATETABLE (

        VALUES ( 'Date'[Date] ),

        REMOVEFILTERS ( 'Date' ),

        USERELATIONSHIP ( 'Date'[Date], 'Comparison Date'[Comparison Date] )

    )

VAR Result =

    CALCULATE (

        [Sales Amount],

        ComparisonPeriod

    )

RETURN

    Result

Custom date filter (for your user to compare 2 sales amount measures) should be from comparison date.
Final result should be like this (instead of brand, product name for example):

You can find more info in:
Standard time-related calculations – DAX Patterns
Comparing different time periods – DAX Patterns

[julsr]

Thanks! That would work, but I have multiple attributes used in one single sheet on PowerBI, so I would like, if possible, something that allows me to show all the measures that I already have (around 15) for the six previous months based on the current date and also allows the user to select other timestamps from the database. One measure, as you mentioned, won't work for what I need, or I need to create more measures. For example, one measure is used in one visualization (line chart), but I don't want to show it as a comparison; the value must be updated based on the date filter.