cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srikanthnama Regular Visitor
Regular Visitor

Filter based on multiple criteria

Hi There, I have some sample data liks the below :

 

Year   Revenue   Type

2015  5758         Actual

2016  9854         Actual

2017  7557         Actual

2018   4983        Actual

2019    9435       Projected

 

I only want to select the revenue of the max year where type = 'Actual' i.e. 4983 of year 2018

 

How can I do this ? Apreciate your assistance and time.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Filter based on multiple criteria

Hi @srikanthnama ,

Last Year Revenue =
VAR _maxYear =
    CALCULATE ( MAX ( Rev[Year] ), FILTER ( ALL ( Rev ), Rev[Type] = "Actual" ) )
VAR _Revenue1 =
    CALCULATE ( MAX ( Rev[Revenue] ), FILTER ( Rev, MAX ( Rev[Year] ) = _maxYear ) )
RETURN
    _Revenue1

Last year revenue.PNG


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

View solution in original post

1 REPLY 1
Highlighted
Nathaniel_C Super Contributor
Super Contributor

Re: Filter based on multiple criteria

Hi @srikanthnama ,

Last Year Revenue =
VAR _maxYear =
    CALCULATE ( MAX ( Rev[Year] ), FILTER ( ALL ( Rev ), Rev[Type] = "Actual" ) )
VAR _Revenue1 =
    CALCULATE ( MAX ( Rev[Revenue] ), FILTER ( Rev, MAX ( Rev[Year] ) = _maxYear ) )
RETURN
    _Revenue1

Last year revenue.PNG


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 350 members 3,274 guests
Please welcome our newest community members: