How to Add Column to get the difference of sold items from current month to previous month

[dlabards]

I want to add a column to get the monthly consumed product based on the cumulative reading. below is the data and the expected output (Consumed Monthly)

[Fowmy]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Display Name"}, 
        {{"Details", 
            (t)=> Table.AddColumn( 
                Table.AddIndexColumn( t, "i",0,1) , 
                "Consumed" , 
                    each try t[Cumulative Meter Reading]{[i]} - t[Cumulative Meter Reading]{[i]-1}  otherwise 0  )  
         }
        }
        )
in
   Table.Combine(#"Grouped Rows"[Details])

[shafiz_p]

Hi @dlabards Try below codes:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Display Name", type text}, {"Cumulative Meter Reading", Int64.Type}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Previous Month", each try if [Index] = 1 or [Display Name] <> Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type)[Display Name]{[Index]-2} then null else Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type)[Cumulative Meter Reading]{[Index]-2} otherwise null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Consumed Monthly", each if [Previous Month] = null then null else [Cumulative Meter Reading] - [Previous Month], Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Previous Month"})
in
    #"Removed Columns"

Output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[dlabards]

@shafiz_p where should i paste this code? apologies as i'm a newbie in powerbi

[shafiz_p]

Open power query > go to Home tab > open advance editor > replace all by the given codes.
See image:

Hope this helps!!

[dlabards]

thanks a lot! it is now working and trying to incorporate in my data

[dlabards]

hello, i'm getting a wrong output when i applied to my datasource. hope you could share the details of the code below

 #"Added Custom" = Table.AddColumn(#"Added Index", "Previous Month", each try if [Index] = 1 or [Display Name] <> Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type)[Display Name]{[Index]-2} then null else Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type)[Cumulative Meter Reading]{[Index]-2} otherwise null),

[AlienSx]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    rows = List.Buffer(Table.ToList(Source, (x) => x)),
    gen = List.Generate(
        () => [i = 0, r = rows{0}, consumed = null],
        (x) => x[i] < List.Count(rows),
        (x) => [i = x[i] + 1, r = rows{i}, consumed = if r{1} <> x[r]{1} then null else r{2} - x[r]{2}],
        (x) => x[r] & {x[consumed]}
    ), 
    result = Table.FromList(gen, (x) => x, Table.ColumnNames(Source) & {"Consumed Monthly"})
in
    result

[dlabards]

thank you for the reply but i'm a newbie in power bi. appreciate if this can be translated to a layman's term please