Solved

How to replicate the MAXIF Excel function

  • 22 September 2023
  • 2 replies
  • 278 views
K
Rank
Userlevel 2
Badge +6
  • Kevin Seasoned Pigmenteer
  • Seasoned Pigmenteer
  • 14 replies

I have a metric that has two dimensions that have pasted values from another source.  What I would like to do is return the last month that there is a value > 0 for a specific row in the metric.  In Excel terms, this would be done with a MAXIF function where I would return the max month with the condition that “Net Installs” is > 0.  From the screenshot below I want to return the month dimension value of Dec 24

Can this be accomplished using just the BY modifier and the MAX aggregation method?  Any insights are very much appreciated.  Thank you in advance. 

 

 

icon

Best answer by Gawain 22 September 2023, 15:01

View original

2 replies

Gawain
Rank
Userlevel 4
Badge +5

Hello Kevin,

You can try to use this formula:
if('Your Metric'>0,Month)[remove lastnonblank : Month]

The first part of the formula return the Month dimension item for all numbers above 0

The second part of the formula will remove the Month dimension and only keep the “max” value being the last Month.

Let me know if it solve your issue!

K
Rank
Userlevel 2
Badge +6

This is excellent...thank you!  Works perfectly.  Since I just wanted to return the date on the “Net Installs” row I added the SELECT modifier to the metric in the IF statement:

IF('Metric'[SELECT: 'Dimension'='Dimension'."Net Installs"]>0,Month)[REMOVE LASTNONBLANK: Month]

 

Before you responded I ended up finding the article below that helped me understand better what is happening with the calculation.  I was in the middle of tinkering in the formula playground when I lucked out and saw that you provided a reply :)  Nonetheless I figured I would pass this along for anyone else that might be interested:

Get the first / last time a value appears in a metric: formula building guide

Reply


Ask your question here!

Terms & ConditionsCookie settings