Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |