cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jinny_le
Frequent Visitor

Value of filter and use in Measure as TopN filter condition

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. 

 
 
 

 

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
V-lianl-msft
Community Support
Community Support

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.

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.