Metric should skip months according to Data Slicer

[EugenioProlog]

I have this report where I show total sales per month, and the growth % from previous month.

As you can see, I have all months on my data slicer. If I hide a month, the calculation still considers the previous month, even if it is hidden. So for example, if I select 2023-01 and 2023-03, it calculates my 2023-03 Growth % based on my 2023-02 month, even if it is hidden.

I want it to calculate based on the previous visible month. So, for example, if I select 2023-01 and 2023-03, the Growth % metric should be calculated considering 2023-01, not 2023-02. 

Here is my metric:

Sales Growth % Monthly =

VAR CurrentSales = [TotalSales]

VAR PreviousSales =

    CALCULATE(

        [TotalSales],

        DATEADD(dim_calendar[dt_date], -1, MONTH) -- Ajuste para o nível de tempo desejado

    )

RETURN

    IF(NOT ISBLANK(PreviousSales),

       DIVIDE(CurrentSales - PreviousSales, PreviousSales, 0),

       BLANK()

    )

Here is the link to my .pbix file:
https://drive.google.com/file/d/1qChX3l17IhGc7F4LUN0VYqf_pwvvEXfD/view?usp=sharing 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[OwenAuger · Accepted solution]

Hi @EugenioProlog 

You can use OFFSET for this.

Here is an example of how you could update the measure (PBIX attached):

Sales Growth % Monthly (Visible Months) = 
VAR CurrentSales = [TotalSales]
VAR VisibleMonths =
    CALCULATETABLE (
        VALUES ( dim_calendar[Year-Month] ),
        ALLSELECTED ( dim_calendar )
    )
VAR PreviousSales =
    CALCULATE (
        [TotalSales],
        OFFSET ( -1, VisibleMonths ),
        REMOVEFILTERS ( dim_calendar )
    )
RETURN
    IF (
        NOT ISBLANK ( PreviousSales ),
        DIVIDE ( CurrentSales - PreviousSales, PreviousSales, 0 ),
        BLANK ( )
    )

Note that dim_calendar[Year-Month] could be replaced with any column of the same granularity that sorts correctly, e.g. "Start of Month".

Does this work for you?