How to find Nearest Min and Max of Selected date

[adiletsh]

I have 2 tables that is filtered with date and party.  So I need to find nearest min and max value:

1. 

The average is taken from the Average (n)(red paind) of the first table

And the minimum and maximum faces are revealed by 10%:

the minimum is the Average(g) * 0.9;

The maximum is the Average(g) * 1.1;

(For example, min = 1279*0.9 = 1151 and max = 1279*1.1= 1407)

2.

After identifying the facet, from the first column "Beginning (g)" (red paint) of the categorization table, we look for the indicators closest to the facets. And from the "General"(green paint) column of this table, all the indicators that are between these faces are summed up.

(For example, this is the sum between 2261 and 1947 = 29130)
 
I've joined two tables with dates. I've found Min and Max values and identifed 'General' :

SWITCH (

TRUE (),

'Table'[beginning_weight] >= 'Table'[Min] && 'Table'[beginning_weight] <= 'Table'[Max] , 'Table'[value]

)

But Min value not including the min value (1150) as on the task, and starts from 1151.

So, how can I find values which includes Nearest Min and Max to summing up correctly "General" value as an example (sum between 2261 and 1947 = 29130). Notice that, 


Thank you in advance!!

[v-cgao-msft]

Hi @adiletsh ,

Not sure. The logic for getting the closest value should be similar to:

The value closest to the minimum --> The minimum value of greater than _min (INT(1279*0.9)) under current date filtering.

CALCULATE(MIN('Table2'[Value]),'Table'[beginning_weight] >= _min)

 Closest to the maximum value --> the maximum value less than _max (INT(1279*1.1)) under the current date filter.

CALCULATE(MAX('Table2'[Value]),'Table'[beginning_weight] <= _max)

If this doesn't work or I misunderstood your needs, please consider providing sanitized files for testing.

How to provide sample data in the Power BI Forum

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[adiletsh]

Thanks for your answear. Your code is working, but it is not that value which I want to get

Let me explain again.

Your code gives me for min value 1175, but I need the 1150 min value.

As you can see in this photo Your code is taking min like 1175 such as  _min (INT(1279*0.9)) equals to 1151. Accordingly, the min value for this code is 1175

Logically nearest min value for 1151 it's 1150 

So, How can I get nearest min value when if this min value is upper than output of your code?

Thanks for answear

[adiletsh]

Thanks for your answear. Your code is working, but the closest minimum value the wrong number that I want to get. 

So, let me explain again. 
When we finding the _min (INT(1279*0.9)) it equals to 1151. 
  

But your code shows as closest minimum value 1175(from red paint column1*). Logically closest minimum value for _min (INT(1279*0.9))=1151) is 1150.

How can I get this value 1150 which placed above 1175.

Thanks for replying