Increase of 5% or more based on min and max date

[Hollys83]

I need to find 

Number of families who have increased their average monthly household income by at least 5%

I was able to group by HHID and date but couldn't figure out how to find the minimum and maximum date with a sum of income then do the comparison.

Apologies if this is not enough info to describe my issue. 

This is my data. 

HHID	Date	Monthly
1	12/20/2023	0
1	3/7/2024	276
1	3/7/2024	2381
2	5/1/2023	0
2	5/1/2023	2798
3	10/2/2023	0
3	10/11/2023	0
3	5/5/2023	2292
3	10/2/2023	2299
3	10/11/2023	3867
4	8/23/2023	0
4	12/5/2023	0
4	1/19/2024	0
4	4/1/2024	0
4	4/7/2023	441
5	11/1/2023	60
5	5/10/2023	739
5	5/10/2023	1046
5	12/19/2023	1190
6	2/21/2023	0
6	2/21/2023	914
6	2/21/2024	914
7	7/31/2023	0
7	4/4/2023	186
7	4/4/2023	841
7	7/11/2023	1013
8	3/13/2023	0
8	4/5/2024	149
8	3/13/2023	841

[Anonymous · Accepted solution]

Hi @Hollys83 ,

Thanks @PhilipTreacy  and @Ashish_Mathur  for the quick reply. I have some other thoughts to add:

(1)We can create measures.

Pre = 
var _min_date=CALCULATE(MIN('Table'[Date]),FILTER(ALLSELECTED('Table'),[HHID]=MAX('Table'[HHID])))
var _min_date_income=CALCULATE(SUM('Table'[Monthly]),FILTER(ALLSELECTED('Table'),[HHID]=MAX('Table'[HHID]) && [Date]=_min_date))
var _max_date=CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),[HHID]=MAX('Table'[HHID])))
var _max_date_income=CALCULATE(SUM('Table'[Monthly]),FILTER(ALLSELECTED('Table'),[HHID]=MAX('Table'[HHID]) && [Date]=_max_date))
RETURN DIVIDE(_max_date_income-_min_date_income,_min_date_income)

Count = CALCULATE(DISTINCTCOUNT('Table'[HHID]),FILTER(ALLSELECTED('Table'),[Pre]>=0.05))

Then the result is as follows.

Best Regards,

Neeko Tang

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

[Ashish_Mathur]

Hi,

Based on the data that you have shared, clearly show the expected result with an explanation.

[Ashish_Mathur]

Hi,

Based on the data that you have shared, clearly show the expected result with an explanation.

[PhilipTreacy]

Hi @Hollys83 

Download example PBIX file

I'm not sure how the dates relate to the problem.  Are you looking for an increase month to month, or from the earliest date to the latest date?

If it's the latter, you can create 2 measures to work out the lowest income and highest income per HHID

Min Income = MIN('Income'[Monthly])

Max Income = MAX('Income'[Monthly])

The increase for each HHID is thus

Income Increase = DIVIDE(([Max Income] - [Min Income]), [Min Income], 1)

NOTE, where the min income was 0, the result of this measure will be 1, otherwise you'll get a divide by 0.  This means in the table below, the increase in such cases is 100%

Regards

Phil

[Hollys83]

Hello and thank you for answering. Yes, I need to find if the income increased from the earliest date to the latest date. 

[PhilipTreacy]

@Hollys83 

OK, so the solution I provided should work for you then?

regards

Phil

[Hollys83]

Rather than finding the max and min income, how do I find the income by max and min date?

Once I get the min and max date and the income associated with those, I could then divide the two income amounts from min and max date. 
The max income could come from the minimum date but I need to find if they had an overall increaseof 5% from beginning to end.