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.

v-karpurapud's avatar
v-karpurapud
Community Support
1 year ago

Hi mail2rohitnagar 

Welcome to the Microsoft Fabric Community Forum!


To create a measure for calculating the Corrected Target based on the previous month's variance, implement a DAX formula that dynamically adjusts the current month's target by adding the difference between the previous month's target and actual values, ensuring it supports month-to-month transitions including year rollovers and accurately reflects both row-level and aggregate totals across Sales Person and specific groupings.


Please refer to the attached .pbix file for your reference and share your thoughts.


Thank you.

mail2rohitnagar's avatar
mail2rohitnagar
Regular Visitor
1 year ago

Hi,

Thank you for the working file, but it is not matching with the desired result. Variance of month 1 needs to added to the corrected target of month 2 for the same sales person and particular. you can check my attached excel from the previous comment where i have highleted the desired result in column V (Corrected Target)

  • v-karpurapud's avatar
    v-karpurapud
    Community Support
    1 year ago

    Hi mail2rohitnagar 

    I have updated the .Pbix as per your requirement. Please take a movement to review it and let us know if you need any adjustments.




    Regards,
    Karpurapu D,
    Microsoft Fabric Community Support Team.

     



    • mail2rohitnagar's avatar
      mail2rohitnagar
      Regular Visitor
      1 year ago

      hi,

      unable to see the formula

       

      • v-karpurapud's avatar
        v-karpurapud
        Community Support
        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.