Forum Discussion

JlmillerRedmond's avatar
JlmillerRedmond
Microsoft Employee
1 year ago
Solved

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 mont...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

     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.

JlmillerRedmond's avatar
JlmillerRedmond
Microsoft Employee
1 year ago

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's avatar
ronrsnfld
Super User
1 year ago

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"