Filtering data visual when slicer selected

[blader1989]

Dear all,

I'm building a  visual that can show previous data - assuming we want the visual will show last 3 months, 6 months....when we click the time in slicer but I can't still figure how to do after browsing all relevant topic even though I created a independent time time.

So it woule be great if you can guide me how to do. 

My apporach:

- The table and independent table time

- DAX for L3M that I wrote: 

L3M =

CALCULATE (

    SUM ( raw[Total (-VAT)] ),

    FILTER (

        ALL ( 'raw' ),

        raw[Billing Date] = MAX (IndependentCalendartbl[Date] )

            && raw[Billing Date] <= MAX (Calendartbl[Date])

            && raw[Billing Date] >= EDATE ( MAX(Calendartbl[Date]), -3 )

    )

)

My problem is when I choose the time slicer (for example May 2021), the visual only shown May -March-Feb like image below. But, it must shown April, March and Feb

When I choose January 2022, it didn't show previous December 2021-Nov 2021

So we need your help to guide us how to fix the DAX to show the outcomes we want: show previous months when we select time in slicer. Thank you so much for your time when reading my post!!  

[Anonymous]

Hi @blader1989,

I think this should be related to the filter effect from table relationships, these filter effect will filter your visualizations with high priority.
For this scenario, I'd like to suggest you create a new unconnected data table as source slicer.
Then you can extract the selection use in Dax expression as condition to control calculation ranges to indirectly filter records on the visual.
Regards,

Xiaoxin Sheng

[blader1989]

Hi @Anonymous , thank you for your reply. I already created a new unconnected date table for my purpose in my PBIX file

So could you please guide me step by step as your comment? Thank you.

[Anonymous · Accepted solution]

Hi @blader1989,

After I replace the max to values function and change the operator to 'in' , the visual seems works well with filter selection. You can try to use following measure formula if help:

L3M2 = 
CALCULATE (
    SUM ( raw[Total (-VAT)] ),
    FILTER (
        ALL ( 'raw' ),
        raw[Billing Date]
            IN VALUES ( IndependentCalendartbl[Date] )
                && raw[Billing Date] <= MAX ( Calendartbl[Date] )
                && raw[Billing Date] >= EDATE ( MAX ( Calendartbl[Date] ), -3 )
    )
)

Notice:

The max function only extracts the single value from aggregate row contents. So it may get the blank result(power bi visuals will auto hide the category that has blank result) if your table records not existed correspond records with date equal to the end date of each category year month group.

Regards,

Xiaoxin Sheng

[blader1989]

Thank you @Anonymous  for your wonderful reply. It solved my problem.

[blader1989]

😁Hello is there anyone that can help us?