cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Asantos2020
Advocate II
Advocate II

Get the max value of a column, limiting the search by a Calendar Table

Hello,

 

This may sound really basic, but I've run out of neurons by now and I need your help.

 

I need to get the max value in a column, but the table is filtered by a Calendar Table.

Table1
Date                 Item                Value
01/01/2019      Item1              2

02/01/2019      Item2              3

05/10/2019      Item3              4

 

I've been using this DAX measure to get the max value:

LowestCost = calculate(min(Table1[Value]);Allexpect(Table1;Table1[Item]))

 

I now need to get this max value, but considering the date range in the Calendar Table I have filtering Table1.

 

Thanks in advance!

 

Antonio Santos

1 ACCEPTED SOLUTION
mwegener
Super User II
Super User II

Hi @Asantos2020 ,

 

try this.

HighestCost =
CALCULATE (
    MAX ( Table1[Value] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

1 REPLY 1
mwegener
Super User II
Super User II

Hi @Asantos2020 ,

 

try this.

HighestCost =
CALCULATE (
    MAX ( Table1[Value] ),
    FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) )
)

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors