Visual filtered by Slicer value -1, -2, and -3

[augustindelaf]

Hello Power BI members,

I am struggling to create dynamically filtered visuals according to a Report Level Filter.

Let me explain : 

When a Report Level Filter is selected [Date of Sampling], some of my visuals should display the value that is filtered, but :

-a visual should display Last Value before selected Value (N-1)

-a visual should display Last Value before N-1 (N-2)

-a visual should display Last Value before N-2 (N-3)

Here is an image for a better understanding !

How Can I do that please ?

Best regards,

Augustin

[v-eachen-msft]

Hi @augustindelaf ,

If you want to use filter, you could use MAX() and MONTH() functions to create four measures like the following DAX:

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        MONTH ( 'Table'[Date] )
            = MONTH ( MAX ( 'Table'[Date] ) ) - 1
    )
)

You could replace "-1" with "-0","-2","-3".

If you want to use slicer you could create a date dimension at first.

Then refer to the following DAX:

Measure =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        'Table',
        MONTH ( 'Table'[Date] )
            = MONTH ( SELECTEDVALUE ( 'Date Dimension'[Date] ) ) - 1
    )
)

 You could replace "-1" with "-0","-2","-3".

[augustindelaf]

@v-eachen-msft any idea ?

To translate my concern into very simple words, my question is : 

 "get value before selected value in a slicer"

thx in advance

[augustindelaf · Accepted solution]

@amitchandak @v-eachen-msft ,

I finally managed by myself to find the solution.

This link helped me :

https://community.powerbi.com/t5/Desktop/DAX-getting-previous-value/td-p/489921

I had to create an index to define previous/next values, and then call this index in calculated columns.

[amitchandak]

Thanks for sharing the solution.

[amitchandak]

If you are using something like this can help you

Rolling last 1 before 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Max(dateadd('Date'[Date],-1,MONTH)),-1,MONTH))  
Rolling 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-1,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[augustindelaf]

Thanks for your message @amitchandak 

I tried your measure "Rolling 1 before 1" and it does not work.

Anyway, I'm wondering if it is the good solution because it seems to be linked with Dates. 

In my need, it is linked to the value Date of Sampling, that is totally random (There can be either 30 or just 2 dates of Sampling, at any time in the year.)

Thanks

[augustindelaf]

@v-eachen-msft  Many thanks for your answer.

I have tried you method. I have a complete date Table [Date] with a [Date] field.

I put your measure with my data model but then when I import it into the visual N-1, it doesn't display.

There is an active relationship between my Date table and the facts table.

[amitchandak]

Rolling last 1 before 1 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],Maxx('Date',dateadd('Date'[Date],-1,MONTH)),-1,MONTH)).

In the above one if you want to make dynamic, create slicer to provide no of period and period type.

[augustindelaf]

@amitchandak The measure does not have any error but the when in a visual, it shows nothing.