Taking max value per id with filters

[Nini1]

Hello everyone,

I'm a new user of Power BI and need your help to solve the problem.

I have a data with the following fields: ID, ID2, date, date1, Amount. ID is unique for each customer, that may have different ID2. I was trying to find  sum of 'amount' for just only those cases and those ID2-s when date is less than  Min value that is created by me and is shown in script below and when date1 is more or equal to that min value. Also I want to sum just those values that have max ID2 per Id within this time restriction. (So trying to find max of ID2 for only those cases when date is less than min value and date1 is more or equal to min value)

Here is a sample data. (ID, ID2, Date, Date1, Amount) e.g. min date is 10/9/2021. I need to sum 'Amount' where 'Date'<mindate and 'Date1' >=mindate and for this time period we should sum 'amount' that has highest ID2.

e.g. for ID=3, we should take into account only 'amount' for ID2=13 and not 10 and 15 because 15 does not have 'Date' less than 'mindate' and 10 is not the highest ID2 for this customer with ID3.  Hope this will clarify the issue I have.

ID	ID2	Date	Date1	Amount	min(date) - 10/9/2021	result
1	2	9/29/2021	10/30/2021	100		100	1700
2	3	9/30/2021	10/30/2021	500		0
2	4	10/5/2021	10/30/2021	800		800
3	10	10/5/2021	11/15/2021	200		0
3	13	10/8/2021	11/15/2021	800		800
3	15	11/2/2021	11/15/2021	1000		0
4	23	1/31/2021	5/1/2021	250		0
4	24	2/2/2021	5/1/2021	300		0

"A single value for column 'ID2' in table 'Tbl' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."I wrote this script but it gives me this error  

Total Amount = 

VAR MIN_N_ = MIN('Tbl'[date])

VAR RESULT_N_ = CALCULATE(MAX('Tbl'[ID2],ALLEXCEPT('Tbl','Tbl'[ID])),FILTER(ALL('Tbl'[date]),'Tbl'[date]<MIN_N_),FILTER(ALL('Tbl'[date1]),'Tbl'[date1]>=MIN_N_))

VAR RESULT_Fin_ = CALCULATE(SUM('Tbl'[Amount]),FILTER(ALL('Tbl'[ID2]),'Tbl'[ID2]=RESULT_N_))

RETURN RESULT_Fin_

 Could you please tell me exactly how can I adjust the script or field so that it could perform without errors and give corresponding analytics?

Thank you in advance

[Anonymous · Accepted solution]

Hi @Nini1 ,

You said that the min date is created by yourself. If you want to make the min date dynamic, you can use the slicer.

Here's my solution.

1.Create a calendar table.

Calendar = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

There's no relationship.

2.Create the following measures.

Measure is to find the rows where the Date column is less than Min Date and the Date2 column is greater than Min Date.

Measure = IF(MAX('Table'[Date])<SELECTEDVALUE('Calendar'[Date])&&MAX('Table'[Date1])>SELECTEDVALUE('Calendar'[Date]),1)

Measure 2 = var _maxid=CALCULATE(MAX('Table'[ID2]),FILTER(ALL('Table'),[ID]=MAX('Table'[ID])&&[Measure]=1))
return CALCULATE(SUM('Table'[Amount]),FILTER('Table',[ID2]=_maxid))+0

Measure3 is to get the correct total value.

Measure 3 = SUMX('Table',[Measure 2])

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Nini1]

@Anonymous Thank you for you response. Yes I'm trying to create min myself just I'm creating it depending on 'Date' field. So I have slicer on 'Date' field I have in my table and was trying to change min (Date) when I change it on slicer. If I insert and substitute Calendar slicer you have with Date field slicer will these steps still work? or creating calendar table is the only way for dynamic min filter ?

Thank you very much

[Anonymous]

Hi @Nini1 ,

If the date field in the main table is used as a slicer filter.

You'll see that the visual is also filtered out, so that doesn't work.

We usually create a calendar table that has no relationship with the main table to get the dates you want to compare.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@Nini1  provide sample data, expected output.

[Nini1]

@smpa01 

Here is a sample data. (ID, ID2, Date, Date1, Amount) e.g. min date is 10/9/2021. I need to sum 'Amount' where 'Date'<mindate and 'Date1' >=mindate and for this time period we should sum 'amount' that has highest ID2.

e.g. for ID=3, we should take into account only 'amount' for ID2=13 and not 10 and 15 because 15 does not have 'Date' less than 'mindate' and 10 is not the highest ID2 for this customer with ID3.  Hope this will clarify the issue I have.

ID	ID2	Date	Date1	Amount	min(date) - 10/9/2021		result
1	2	9/29/2021	10/30/2021	100		100	1700
2	3	9/30/2021	10/30/2021	500		0
2	4	10/5/2021	10/30/2021	800		800
3	10	10/5/2021	11/15/2021	200		0
3	13	10/8/2021	11/15/2021	800		800
3	15	11/2/2021	11/15/2021	1000		0
4	23	1/31/2021	5/1/2021	250		0
4	24	2/2/2021	5/1/2021	300		0