Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.