Forum Discussion
Evaluating Multiple fields to Calculate a Value
I have tables for multiple years by month, showing active user counts for customers. Also included in the table is sales data for those customers.
Each month, I need to evaluate the current month user count compared to the immediately preceding user count, determine which is higher, and record that as the highest user count, if and only if the customer made a purchase in the month. If there was no purchase, the prior month high amount is returned. Sales do not always match changes in user counts and user counts may change without sales.
Here is a table of hypothetical customer flows showing how various scenarios should be recorded:
| Customer | Month | PriorYrHigh | SalesLast3Mo | CurrentUserCount | ReturnedHigh |
| A | 1 | 10 | 0 | 15 | 10 |
| B | 1 | 10 | 1 | 15 | 15 |
| C | 3 | 15 | 0 | 75 | 15 |
| D | 4 | 20 | 1 | 21 | 21 |
| E | 1 | 10 | 5 | 5 | 10 |
| F | 3 | 10 | 10 | 15 | 15 |
| G | 4 | 15 | 5 | 15 | 15 |
My current logic isn't working and efforts to fix result in a circular logic error.
Hi JlmillerRedmond ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.
Thank you.
12 Replies
Thanks for reaching out to the Microsoft fabric community forum.
I would also take a moment to personally thank dufoq3, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
To resolve the circular dependency and compute RecordedHigh correctly, reference previous months' values without directly using the current RecordedHigh. In Power Query, add a custom column to compute the RecordedHigh incrementally.
- create a new column for RecordedHigh using DAX in the Power Query Editor. Use the following DAX:
if [SalesLast3Mo] > 0 then
List.Max({[PriorYrHigh], [CurrentUserCount]})
else
[PriorYrHigh]
This will calculate the RecordedHigh based on the rules you specified:
- If SalesLast3Mo > 0 (purchase was made), it takes the max of CurrentUserCount or PriorYrHigh. If SalesLast3Mo = 0 (no purchase), it just takes the PriorYrHigh.
- Add the RecordedHigh column, then use Fill Down in the Transform tab to carry forward values from the previous row. This ensures missing values are filled with the value from the row above.
I implemented it on my end using sample data, and it is working correctly. I am including the PBIX file for your reference.
If you still face issues:
- Ensure that your data is sorted by Customer and Month in ascending order before applying the calculations.
- Check for any blanks or invalid data in the columns, especially in PriorYrHigh or SalesLast3Mo.
I hope this should resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thank you.
Hi JlmillerRedmond ,
We haven't received a response from you regarding our previous suggestions on Evaluating Multiple fields to Calculate a Value. We would appreciate your feedback to ensure we can assist you further.
If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Let us know if there’s anything else we can do to help.
Thankyou.
Hi JlmillerRedmond ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Formatting in original message made table hard to read. Here is a screen snip.
Hi JlmillerRedmond, I'm not sure if I understand the logic. I tried this one with 1 difference:
if [SalesLast3Mo] > 0 then [CurrentUserCount] else [PriorYrHigh]Whole query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYQMgAcKGphBerE60khOypCFc0hQs6QxkGsPEQArMkSRdgEwTIDaC6TSCEiBJV2RjTaEYaqUbzFQDOIFkrDvUWEO4LphkLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Month = _t, PriorYrHigh = _t, SalesLast3Mo = _t, CurrentUserCount = _t, ReturnedHigh = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"SalesLast3Mo", type number}, {"CurrentUserCount", Int64.Type}, {"PriorYrHigh", Int64.Type}, {"ReturnedHigh", Int64.Type}}), Ad_Result = Table.AddColumn(ChangedType, "Result", each if [SalesLast3Mo] > 0 then [CurrentUserCount] else [PriorYrHigh], Int64.Type) in Ad_ResultTo maybe make it clearer, here is hypothetical for one customer with data in multiple months. The customer makes purchases some months, but user count doesn't change by the same amount or change at all. The RecordedHigh needs to go up when user count rises, but only if purchases are made.
Looking at month 7 above, user count goes up, but there are no purchases. For that month RecordedHigh should be the prior month recorded high. In some months, purchases may be made but user count drops. RecordedHigh remains the prior month amount.
Output:
Custom column code:
if [SalesLast3Mo] > 0 then List.Max({[PriorHigh], [CurrentUserCount]}) else [PriorHigh]Whole query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUTIBYiMDIGEIYkCJWB2IpClMzBSmCknSDFnS0AJV0hwmCdZkjippAZM0BMuaQAiYrCVMDJskQocJNllDmCDCwUDZWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Month = _t, PriorHigh = _t, SalesLast3Mo = _t, CurrentUserCount = _t, RecordedHigh = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"PriorHigh", type number}, {"SalesLast3Mo", type number}, {"CurrentUserCount", type number}, {"RecordedHigh", type number}}), Ad_Result = Table.AddColumn(ChangedType, "Result", each if [SalesLast3Mo] > 0 then List.Max({[PriorHigh], [CurrentUserCount]}) else [PriorHigh], type number) in Ad_Result
Hi JlmillerRedmond ,
Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster.
If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.
Thank you.
