Month on Month Difference

[Anonymous]

Hi everyone! 

I am having trouble digging out a formula to get the month on month difference for each Account Number.

My data is structed as below:

Account	Date	Limit	Difference
1	07/05/2019	€        8,000
2	07/05/2019	€        5,000
3	07/05/2019	€        1,000
1	15/06/2019	€        6,000
2	15/06/2019	€        7,000
3	15/06/2019	€        3,000
1	12/07/2019	€        9,000
2	12/07/2019	€        6,000
3	12/07/2019	€        5,000

My issue is how to find the difference in the limit's for each account and how it differs month to month.

Can anyone please help? I would greatly appreciate it.

Thank you

Water

[v-eachen-msft]

Hi @Anonymous ,

At first, you need to use the following codes in query editor to get a suitable table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxOzi/NK1HSUXJJLEkFUj6ZuZlgbmZaWmpRal5yqlKsTrSSIVDIyMDQUtdU1xzIVHjUtEYBCix0DAwMQGJghUa4FZqiKDTGrdAQRSHcajNdQ1M0lWbY7cai0hy75VhUGmO33VzX0AhNpSV227GoNMNuOxaVSIEUCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Account", Int64.Type}, {"Date", type text}, {"Limit", type text}, {"Difference", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Difference"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns","        ","",Replacer.ReplaceText,{"Limit"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","€","",Replacer.ReplaceText,{"Limit"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",",","",Replacer.ReplaceText,{"Limit"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value2",{{"Limit", type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type2", {{"Account", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type2", {{"Account", type text}}, "en-US")[Account]), "Account", "Limit", List.Sum),
    #"Added Index" = Table.AddIndexColumn(#"Pivoted Column", "Index", 1, 1)
in
    #"Added Index"

Then you will get a table.

Now you can create columns to calculate differences.

Compare1to2 =
'Table'[1] - 'Table'[2]

Compare2to3 =
'Table'[2] - 'Table'[3]

Compare1to3 =
'Table'[1] - 'Table'[3]

D1 =
VAR p =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[1], 1 ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF ( p <> BLANK (), 'Table'[1] - p )

D2 =
VAR p =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[2], 1 ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF ( p <> BLANK (), 'Table'[2] - p )

D3 =
VAR p =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[3], 1 ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    )
RETURN
    IF ( p <> BLANK (), 'Table'[3] - p )

The final table:

Best Regards,

Eads

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

[Anonymous]

Wow!

Thank you for such a thorough answer.

The solution is too complicated for me to implement but I appreciate your time and effort nonetheless.

Thank you

Stay Hydrated

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

Have you tested my solution? If my answer is useful, please accept my reply.Thanks!

Best Regards,

Eads

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