Evaluating Multiple fields to Calculate a Value

[JlmillerRedmond]

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.

[v-tsaipranay · Accepted solution]

 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.

[v-tsaipranay]

Hi @JlmillerRedmond 

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:

1. Ensure that your data is sorted by Customer and Month in ascending order before applying the calculations.
2. 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. 

[v-tsaipranay]

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.

[v-tsaipranay]

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.

[JlmillerRedmond]

While I appreciate the efforts of responders, proposed solutions have not solved the problem.  Kudos to those who've taken the time to look at this and attempt to help.  I'm still working on a solution.  If I come up with one, I'll share it here.

[v-tsaipranay]

Hello @JlmillerRedmond ,

Could you please confirm if the issue has been resolved. If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

Thank you.

[dufoq3]

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_Result

[JlmillerRedmond]

To 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.

[ronrsnfld]

I think I understand what you need using a single customer as you show. (For multiple customers, you could do the same thing within a Table.Group function).

For the Source data, the only columns needed are Customer, Month, SalesLast3Mo, and CurrentUserCount.

The List.Generate function can then be used to calculate the PriorHigh and RecordedHigh columns, with no circular reference errors.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUTIBYkMgNjJUitWBCJlCsZEBXMgMKmRoARcyB2IDkCpzuJAFSAVYzAQuZglThhACKzFBEzNEsjQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Month = _t, SalesLast3Mo = _t, CurrentUserCount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Customer", type text}, {"Month", Int64.Type}, {"SalesLast3Mo", Int64.Type}, {"CurrentUserCount", Int64.Type}}),
    
    #"Add Recorded High" = 
        [a=List.Generate(
            ()=>[ph=null, rh=#"Changed Type"[CurrentUserCount]{0}, idx=0],
            each [idx] < Table.RowCount(#"Changed Type"),
            each [ph=[rh], rh=if #"Changed Type"[SalesLast3Mo]{[idx]+1}=0 
                        then [rh] 
                        else List.Max({[rh],#"Changed Type"[CurrentUserCount]{[idx]+1}}), idx=[idx]+1],
            each {[ph],[rh]}),
         b=List.Transform(a, each _{0}),
         c=List.Transform(a, each _{1}),
         d=Table.FromColumns(
             Table.ToColumns(#"Changed Type")
             & {b} & {c}, type table[Customer=text, Month=Int64.Type, SalesLast3Mo=Int64.Type, 
                               CurrentUserCount=Int64.Type, PriorHigh=Int64.Type, RecordedHigh=Int64.Type]
         )][d],
    
    #"Reordered Columns" = Table.ReorderColumns(#"Add Recorded High",
        {"Customer", "Month", "PriorHigh", "SalesLast3Mo", "CurrentUserCount", "RecordedHigh"})
in
    #"Reordered Columns"

[dufoq3]

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

[JlmillerRedmond]

The calculated column text would work except that PriorHigh has to be calculated each month as well.  It is the value in RecordedHigh for the preceding month.

Because RecordedHigh uses PriorHigh to determine which result to return, and because PriorHigh depends on RecordedHigh to return a result, a circular dependency occurs.

My formula for RecordedHigh essentially matches the proposed solution.  But breaks because PriorHigh = RecordedHigh for Month-1

[JlmillerRedmond]

Formatting in original message made table hard to read.  Here is a screen snip.