Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 |
Solved! Go to 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.
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.
Hi,
Based on the data that you have shared, clearly show the expected result with an explanation.
Hi,
Based on the data that you have shared, clearly show the expected result with an explanation.
Hi @Hollys83
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
Proud to be a Super User!
Hello and thank you for answering. Yes, I need to find if the income increased from the earliest date to the latest date.
OK, so the solution I provided should work for you then?
regards
Phil
Proud to be a Super User!
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.
User | Count |
---|---|
123 | |
76 | |
63 | |
50 | |
50 |
User | Count |
---|---|
175 | |
125 | |
60 | |
60 | |
58 |