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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
adiletsh
Frequent Visitor

How to find Nearest Min and Max of Selected date

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

1. 

adiletsh_0-1673801002965.png

 

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.

adiletsh_1-1673801080623.png

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!!


3 REPLIES 3
v-cgao-msft
Community Support
Community Support

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

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.

adiletsh_0-1673893395472.png

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

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. 
  

adiletsh_0-1673885810277.png

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.