Forum Discussion

mail2rohitnagar's avatar
mail2rohitnagar
Regular Visitor
1 year ago
Solved

Need help with calculated column

I have a usecase to calculate corrected target based on the previous month variance. Below is my scenario   Column Name Values(M1) Values(M2) Values(M3) Sales Person Name John John Jo...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hi 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1djbbtowAAbgV0G5hsgH4oTLduukIjFQe9GLqqooyzYkaKoUOu1mF3urvc6eZHYI1A7GJwXHk1pyahM+nIQ///19NIz6EQIooZO75Wq1nK/p3KwsvmwXm1c6Oy6+P9PJZLkoi9fi66YxP8WE/evf3396OAbZfhb0M4z7GNN1gK4DMaxeMZtgQLfFGLMDJ78QoVP6c/FSDqp3wQ74yHb7COAADAf1m2O7hXGa9T6UdKm3PyC3COixdoswxqA3oOvYrh/691ESCHIy/ylDJjzynaFGVhtqJNsyjHFWYUkg2PH2WYYlIhYTEyyKU8RjSQzTCpsGg13JsCmPxYYjCxojm8QZrLBZINiL7TcZNmvjNMb7kR0Fgr3NX2TYURvYw2kMQSDa6WIj0ULQrhYGov1cvMm0sF0tCkT7MV/ItKhd7Q5LusaO5/TbhxxdttXQkrawKBDsp/xJhkWtYnEg2Mm8lGFxq1guFt8UT3nJBLd5+bZc5AbWWfEjLy+vD28GpvUs7BNC6G+61+LqFbDjJKBadzITz6Z3VzeX18exuIFlhzuRK+gG4auW7qIRj7vDitmYx6risTGWbWnG4+6wYjbmsaQdLL1oGvG4S+zqBDZtFZuFgBWzMY9txmNoiCVS7CgErJiNeawqHtuMLDqKx91pxWzMaZXx2P485uNxd1oxG/NaVTx20KIQtGI25rWqeOygjQ4hqsMbspCN+cu2GY+xIXYoxaIQsGI25rGqeGwzsqAZj7sMUeUJrCoe25/Gutb4ar2eK7TcswA6e2sMxNYYmbTGuz88jsedYk3aYxRnwARbfzDofQmb9se+uA79sWJsxf6YfUxG/bE/rHV/rBnbkTC2NVfXIHu7bu0b5JNcdNQg1096ugbZF9ahQTbGDunzgVmD7Evr0CA7aHUNsi+tQ4PsoNU1yL60Dg2ygzZS94zebsn2DbI9Vtcg+8I6NMj2WF2D7C9IWTfI9lhNg6yx/h8Nsiwid4c9e4NcP/xoGmQ/2EAaZF/YIBpkT9dsGA2yH2woDbIfbSgNsh9tKA2yH61hgwwNtbulDPNLZh2yp1vyeTtkuH+I13TIfrCBdMi+YpSHDvnhHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MONTH = _t, YEAR = _t, #"PRINCIPAL MANAGERS" = _t, #"PROD/ SERVICE" = _t, #"SALES PERSON" = _t, PRINCIPAL = _t, PRATICULARS = _t, REFERENCE = _t, #"REV, RS. LAKHS" = _t, REV = _t, #"MARGIN %" = _t, #"MARGIN (RS. LAKHS)" = _t, MARGIN = _t, #"Financial Year" = _t, #"BUD. / ACT." = _t, #"MONTH YEAR" = _t, #"Unique Id" = _t, #"Actual Sales" = _t, #"Target in Cr" = _t, #"Target Margin in Cr" = _t, Variance = _t, CorrectedTarget = _t]),

    
    Clean1 = Table.ReplaceValue(Source, "₹", "", Replacer.ReplaceText, {"Target in Cr", "Actual Sales"}),
    Clean2 = Table.ReplaceValue(Clean1, "Cr", "", Replacer.ReplaceText, {"Target in Cr", "Actual Sales"}),

  
    ChangedTypes = Table.TransformColumnTypes(Clean2, {
        {"MONTH", Int64.Type}, {"YEAR", Int64.Type},
        {"Target in Cr", type number}, {"Actual Sales", type number}
    }),

    
    #"Removed Old CT" = Table.RemoveColumns(ChangedTypes, {"CorrectedTarget"}),

    
    Sorted = Table.Sort(#"Removed Old CT", {
        {"SALES PERSON", Order.Ascending},
        {"PRATICULARS", Order.Ascending},
        {"YEAR", Order.Ascending},
        {"MONTH", Order.Ascending}
    }),

  
    Columns = Table.ColumnNames(Sorted),
    Rows = Table.ToRows(Sorted),

    AddCorrectedTarget = List.Accumulate(
        Rows,
        { {}, 0, null, null },  
        (state, currentRow) =>
            let
                result = state{0},
                prevCT = state{1},
                prevSP = state{2},
                prevPart = state{3},

                salesPerson = currentRow{List.PositionOf(Columns, "SALES PERSON")},
                particular = currentRow{List.PositionOf(Columns, "PRATICULARS")},
                target = try Number.From(currentRow{List.PositionOf(Columns, "Target in Cr")}) otherwise 0,
                actual = try Number.From(currentRow{List.PositionOf(Columns, "Actual Sales")}) otherwise 0,

                isFirst = List.Count(result) = 0 or salesPerson <> prevSP or particular <> prevPart,
                newCT = if isFirst then target else prevCT - actual + target,

                rowWithCT = Record.AddField(Record.FromList(currentRow, Columns), "CorrectedTarget", newCT),
                updatedRows = List.Combine({result, {rowWithCT}})
            in
                {updatedRows, newCT, salesPerson, particular}
    ){0},

    FinalTable = Table.FromRecords(AddCorrectedTarget)
in
    FinalTable


    Regards,
    Karpurapu D.

Poojara_D12's avatar
Poojara_D12
Super User
1 year ago

Hi mail2rohitnagar 

To calculate the Corrected Target and Variance in Power BI based on the previous month’s performance, you can approach this by creating calculated columns in your table, especially since your data appears to be in a flat structure with one row per salesperson, per product, per month. The corrected target is calculated as the current month's target plus the variance from the previous month, where the variance is defined as previous month’s target minus actual sales. Since this logic requires referencing the previous row (i.e., the same salesperson and product in the previous month), you can use DAX with functions like EARLIER() and FILTER() to look back at the corresponding record for the previous month. First, you calculate the previous month’s variance by filtering the table for the same salesperson, same product, same year, and one month earlier, then subtracting actual sales from the target for that month. Next, you add that previous variance to the current month’s target to get the corrected target. Finally, you compute the current month’s variance as the difference between the corrected target and actual sales. This approach works well with calculated columns when your data is in a row-by-row format. If you were working with a more dynamic or time-aware model (e.g., using slicers or visuals based on a calendar), then measures with a proper date table and time intelligence functions like DATEADD or PREVIOUSMONTH would be more appropriate.