Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
deblacus
Frequent Visitor

Power Query Formula

Hi,

 

I'm looking for a power query m function to insert a new month column and calculate the variance between one month to previous month i.e. oct ytd - sep ytd = oct mtd etc.   based on the data shown below produced in excel.

 

many thanks in advance

 

NL CodeNominal NameGroupAmountReporting TypeMonthMonth Movement
4000HatsCommercial Sales600Actual31/10/20xx150
4000HatsCommercial Sales450Actual30/09/20xx250
4000HatsCommercial Sales200Actual31/08/20xx 

 

 

Power Query

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

Hi @deblacus ,

Thanks for all the replies!

And @deblacus , I have expanded the sample data according to your needs. Please check whether this is the result you want:

let
    Source = Table.FromRows({
        {4000, "Hats", "Commercial Sales", 600, "Actual", "10/31/2024"},
        {4000, "Hats", "Commercial Sales", 450, "Actual", "9/30/2024"},
        {4000, "Hats", "Commercial Sales", 200, "Actual", "8/31/2024"},
        {4001, "Scarves", "Commercial Sales", 300, "Actual", "10/31/2024"},
        {4001, "Scarves", "Commercial Sales", 400, "Actual", "9/30/2024"},
        {4001, "Scarves", "Commercial Sales", 500, "Actual", "8/31/2024"},
        {4002, "Gloves", "Commercial Sales", 150, "Actual", "10/31/2024"},
        {4002, "Gloves", "Commercial Sales", 100, "Actual", "9/30/2024"},
        {4002, "Gloves", "Commercial Sales", 75, "Actual", "8/31/2024"}
    }, {"NL Code", "Nominal Name", "Group", "Amount", "Reporting Type", "Month"}),
    ChangedType = Table.TransformColumnTypes(Source, {{"Month", type date}}),
    SortedTable = Table.Sort(ChangedType, {{"NL Code", Order.Ascending}, {"Month", Order.Descending}}),
    AddedIndex = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),
    AddMonthMovement = Table.AddColumn(AddedIndex, "Month Movement", each let
            CurrentIndex = [Index],
            PreviousRow = try AddedIndex{CurrentIndex - 2} otherwise null,
            PreviousAmount = if PreviousRow <> null and [NL Code] = PreviousRow[NL Code] then PreviousRow[Amount] else null
        in
            if PreviousAmount <> null then PreviousAmount - [Amount] else null),
    RemovedIndex = Table.RemoveColumns(AddMonthMovement, {"Index"})
in
    RemovedIndex

And the final output is as below:

vjunyantmsft_0-1730425133649.png


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

View solution in original post

Omid_Motamedise
Super User
Super User

Copy the following code and past it into Advance Editor to see the result

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMFDSUfJILCkGUs75ubmpRcmZiTkKwYk5qSAhM7C8Y3JJaWIOkGFsqG9ooG9kYGSkFKtDhHYTU1TtBvoGliRoN8Kw3cACqj0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"NL Code" = _t, #"Nominal Name" = _t, Group = _t, Amount = _t, #"Reporting Type" = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NL Code", Int64.Type}, {"Nominal Name", type text}, {"Group", type text}, {"Amount", Int64.Type}, {"Reporting Type", type text}, {"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try [Amount]-Table.Max(Table.SelectRows(#"Changed Type", (x)=> x[Month]<[Month]),"Month")[Amount] otherwise "")
in
    #"Added Custom"

 

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h

View solution in original post

7 REPLIES 7
Omid_Motamedise
Super User
Super User

Copy the following code and past it into Advance Editor to see the result

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjEwMFDSUfJILCkGUs75ubmpRcmZiTkKwYk5qSAhM7C8Y3JJaWIOkGFsqG9ooG9kYGSkFKtDhHYTU1TtBvoGliRoN8Kw3cACqj0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"NL Code" = _t, #"Nominal Name" = _t, Group = _t, Amount = _t, #"Reporting Type" = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"NL Code", Int64.Type}, {"Nominal Name", type text}, {"Group", type text}, {"Amount", Int64.Type}, {"Reporting Type", type text}, {"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each try [Amount]-Table.Max(Table.SelectRows(#"Changed Type", (x)=> x[Month]<[Month]),"Month")[Amount] otherwise "")
in
    #"Added Custom"

 

If my answer helped solve your issue, please consider marking it as the accepted solution. It helps others in the community find answers faster—and keeps the community growing stronger!
You can also check out my YouTube channel for tutorials, tips, and real-world solutions in Power Query with the following link
https://youtube.com/@omidbi?si=96Bo-ZsSwOx0Z36h
Anonymous
Not applicable

Hi @deblacus ,

Thanks for all the replies!

And @deblacus , I have expanded the sample data according to your needs. Please check whether this is the result you want:

let
    Source = Table.FromRows({
        {4000, "Hats", "Commercial Sales", 600, "Actual", "10/31/2024"},
        {4000, "Hats", "Commercial Sales", 450, "Actual", "9/30/2024"},
        {4000, "Hats", "Commercial Sales", 200, "Actual", "8/31/2024"},
        {4001, "Scarves", "Commercial Sales", 300, "Actual", "10/31/2024"},
        {4001, "Scarves", "Commercial Sales", 400, "Actual", "9/30/2024"},
        {4001, "Scarves", "Commercial Sales", 500, "Actual", "8/31/2024"},
        {4002, "Gloves", "Commercial Sales", 150, "Actual", "10/31/2024"},
        {4002, "Gloves", "Commercial Sales", 100, "Actual", "9/30/2024"},
        {4002, "Gloves", "Commercial Sales", 75, "Actual", "8/31/2024"}
    }, {"NL Code", "Nominal Name", "Group", "Amount", "Reporting Type", "Month"}),
    ChangedType = Table.TransformColumnTypes(Source, {{"Month", type date}}),
    SortedTable = Table.Sort(ChangedType, {{"NL Code", Order.Ascending}, {"Month", Order.Descending}}),
    AddedIndex = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),
    AddMonthMovement = Table.AddColumn(AddedIndex, "Month Movement", each let
            CurrentIndex = [Index],
            PreviousRow = try AddedIndex{CurrentIndex - 2} otherwise null,
            PreviousAmount = if PreviousRow <> null and [NL Code] = PreviousRow[NL Code] then PreviousRow[Amount] else null
        in
            if PreviousAmount <> null then PreviousAmount - [Amount] else null),
    RemovedIndex = Table.RemoveColumns(AddMonthMovement, {"Index"})
in
    RemovedIndex

And the final output is as below:

vjunyantmsft_0-1730425133649.png


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

juju63
Regular Visitor

hello deblacus

 

if I well understood your need, why not to use index 0 & index 1 columns ?

just add 2 columns index 0 & index 1 then merge the query with itself on those columns and just select the wanted column.

 

this way you can have month value & month +/-1 value and make any needed calculations

 

hopefuly this can help you 

Ahmedx
Super User
Super User

pls try this

Screenshot_2.png

Many thanks for the replies, I meant to say I want to apply a variance between all rows not just the three shown.

so basically I want to see the month movement in a each product/line item.   

Hope I explained myself better !

pls share data and show expected result

shafiz_p
Super User
Super User

Hi @deblacus 
Create function by using the below M code:

let
    CalculateVariance = (inputTable as table) as table =>
    let
        #"Changed Type with Locale" = Table.TransformColumnTypes(inputTable, {{"Month", type date}}, "en-GB"),
        #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Amount", Int64.Type}}),
        #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month.1", each Date.Month([Month]), Int64.Type),
        #"Sorted Rows" = Table.Sort(#"Inserted Month",{{"NL Code", Order.Ascending}, {"Month", Order.Descending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
        #"Added Previous Amount" = Table.AddColumn(#"Added Index", "Previous Month Amount", each try if [Index] < Table.RowCount(#"Added Index") and [NL Code] = #"Added Index"{[Index]}[NL Code] then #"Added Index"{[Index]}[Amount] else null otherwise null, type number),
        #"Added Variance" = Table.AddColumn(#"Added Previous Amount", "Variance", each [Amount] - [Previous Month Amount], type number),
        #"Removed Unnecessary Columns" = Table.SelectColumns(#"Added Variance", Table.ColumnNames(inputTable) & {"Variance"})
    in
        #"Removed Unnecessary Columns"
in
    CalculateVariance

Rename function to Variance. See image below:

shafiz_p_0-1730372739413.png

 

Now call this function in your source query. See image:

shafiz_p_1-1730372817208.png

 

You are done. 

 

You can also achieve this using the self merging technique. Need only 2 index column : First one is starting from 1 and second one is starting from 0. 

Now merge Index from 1 with the index from 0 and expand one column amount and substract.


Hope this helps!!

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

 

Best Regards,
Shahariar Hafiz

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.