Value of filter and use in Measure as TopN filter condition

[jinny_le]

Hi all, 

I would like to seek guidance regarding how to use a measure as condition for TopN filter. 

My dashboard has 2 visual 

- 1st visual shows monthly sales 

- 2nd visual shows P6M monthly sales trend 

I have 1 filter which is Month, this allow to pick 1 single month value at a time (Sep'19, Oct'19, Nov'19 etc). This works for 1st visual. For 2nd visual, i use "Edit interaction" to deselect the visual interation with Date filter. And it is automatically pick 6month from the latest month data available.

Objective: I want the P6M visual to show sales ending the month i selected in Date filter, instead of the latest month data available. For example, if i choose Feb'18, the 2nd visual will shows trend from Sep'17 to Feb'18. 

Help needed: i created a measure call "Filtered date" to use as condition for TopN filter. But it doesn't work. Could you give me a suggestion how to solve this? Thanks so much! 

Filtered Date = ALLSELECTED('tablename'[Date])

Somehow i'mm not allowed to share photos here to show my example. Let me know if it's not clear. 

[V-lianl-msft · Accepted solution]

Hi @jinny_le ,

First ,if you don't have a unrelated calendar table, you need to create a new calendar table and use the date as a slicer.

Then create a measure to calculate sales:

sum_Sales_last_6_months =

CALCULATE (

    SUM( Sales[Sales] ),

    FILTER (

        ALL ( Sales[Date] ),

        Sales[Date] <= MAX ( DimDate[Date] )

            && Sales[Date] >= EDATE ( MAX(DimDate[Date]),-6 )

    )

)

You can also refer to the pbix.

If the problem persists,could you please share sample data or sample pbix?(Please mask any sensitive data before uploading)

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

[jinny_le]

Hi @V-lianl-msft : thank you so much for sharing this!