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)

[AlienSx · Accepted solution]

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

[shafiz_p · Accepted solution]

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

[v-stephen-msft]

Hi @dlabards ,

Thanks for reaching out to our community.

The methods provided by the users in the posts are all great and correct.

All you need to do is replace the code after their Source= with your own data source code, a common way is to copy and paste it in.

For example, I replaced Alien's source code with my own source code, and then the result is below.

In addition to replacing the source code, I've also replaced the step name (because if the data type is not modified, the calculation will return an error, which is the result of testing). When you replace the step, the function will be called from #"Changed Type".

This is the reulst from Alien.

Methods from Alien, Shahariar, and Fowmy are all correct, if your problem is solved, please accept them.

I've created all three methods well. In the PBIX file, you can download the attachment to view.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy · Accepted solution]

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 · Accepted solution]

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),

[shafiz_p]

Hi @dlabards Download the file and check the implementation in power query. Replace table and column name according to your table and column name.

previous value for different group and month 

Hope this helps!!

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

Best Regards,
Shahariar Hafiz

[AlienSx · Accepted solution]

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