I want to calculate Month Over Month Percentage
I have this data
1 Like
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XVixri23DfyXC5e6gkSKFFkaCFwYSFKkfHBhGw9pgsQwYgP+e492xV3KxakGWkmj4Qx5vnz5IP389pdfP4k+ysc37FKGzTr044fy5UM+//7jHxvqZYxZ2uiV5QI7H2jvXoi0dr/RmVAfVISo2l5p53dplGkjNuV+fpe9qHm9MPv8/rf/vgu7FhKpc1wg8YnOUbjNShudCZ0mpZNUv7ckP1ayNHyXa+83CvA/DzhFyxyt6g3qAY7ZypwgYV+0J3SKFDOp5EHfsVKtaBtxl35+V1hxKg/q/fPb3/69QfNWevM6Ht5frJc2ceLeq+2rjAPtxhe3upn//NfXXx5QRyvsXNu95zxAZik+qTa+v9sPlAy/ZpU39ZrQCRX1xpX3eeXznz///72oUOnSg0A8WkaZtDi+674f5h//+/3dFAz2Ae1u7tuB9klYylX3tn6gPA3vBiVxHDijw0CxguKxn/xvX38O9vHgpODhYf/FCCzhNmxVZrCUURy2aMemHvJ9Ubyae3GjyvtIdqwVXvIfISYIOKPaqEzlKn2X8fc/Qt28t4WABdrvFgJ+wEUidp2tyr7POFBZdYz7TLr10hI6h5VmXMeW0ndff9qIwVQYHG36LEEQKIp/mUq8Sz/QgeI3pkcs+bM+GBvO6j1M5cWoDNAni9xgLy0UgzrlqRjYza8vAa3hq1rV4jwZHTKupfPwuPurqDTRWc13iV6OchOAYpEGUsdb+YEpUaHBj2pnwpYuJwzH68030QE2VP4wi9rejnEfxmbhYY8PecZw/a6va+Z12hx1YLXNLZzLLfYrzmICyKK8XgxngdJdtA4Lpb+oOBWjWedm7TaDLRq8b4dR7GdKEEqWUAVQ1DY9S6A4/MktigMyzQubW3GGidy2Nu5iDnDZBIThFlX3ogbfMmqVbL/hVVSPMib8HS7A8RgZXczR2nMXpBwog3NbkrN9lTfhqK8n1rCeTmc2wqFZUOc9LppRc/ghnmsrGU+Z0SUtgbvvAjnTsdEER/BD2hxl0NsqO4u8PtNRUOi2fLaHgR+pS1jaqM4eQsg5Rm3ZFqr91N6FCjQL+4kq0SOpaMqyitiU+4miEgyS5udpchoNMMyzV6J9mxQp8BBl5GqP8xxxw+tp2pMKllCT9d4cysWBXgyBWwxp4iNCN6dJQ8QZjP3tSxKqUiY9nUd29bmqHbbdnh7qxRg3dIVpcXwyp0FTGCWEO0MGydGR/uAWCRUqSJiPURS82aPMjHaEouJFQvB5LTK6uGvK/7yyYU9dbRsFdWkl4kAW5dF65TTo0Gybbxuph+M3ODeBIJ1BQrJ8aGuSh3nNZOpTsQwu2vSov23cCl212kZI/cVEV4s0a+ew2LQOfQoz1EpxlBdbaoUA+OHVclKAGiGL+oDJZsPv3i539uf+LzqnX5Ef9kzZ8VHO6OYelz0wqBGNfWWLp8q2TtDjEIqV2WYF7aGvj0ps+GLq67DwNIvAe7HVMbmOOCjlODAcRpExux/dlbrphtxkVtGjbbzPiUaKrT3hMxIGoaEX6NF8RJ3enMnKirf3aCkKZpei2iOXs53rWLOQ706n/yUlCNJvb7y0AxV0JR0XfLrmDE6EhHau9Aj4RSFvDCWCKvZ4+9fqryaUH13QOUDphIhXS9cjmTLKuCcjfrYb5SHJQR1hatuGHN3H0+OPq/3aVsXtRCHH1Z/uJrNbHoR8wD2oBkE5IDoGM1lzhYblpnWdYQFQ3K6qFAHSl/ot7mgJQtkXR+mPGTp9MRdGY/b4VM+Z486LnEocN8iJw0MunxpPSaUdl1SpxzwL9R+hgh66Qay7T6QcHI6nWh6/BYmv5pUKg0ObEc0Z5cnJV1MPdZAEqylUILpVxNs2zzFDVqwMjW6Q24GuBoNbD4LmMUg0pA6y9elNMmpIAMUcF5I8jdyZbu0808v1F8TuozFHvMOWZctFOHDH6J262thwDY6Y0x6xZudcD2Lohv/yP8GNkcCqOHyFkv0N+LR0jVnIEgSTAi9WScM1z54WeRPxRnb2pn31SBy9Afg+UEyp6hLS0WxkOi6tSkTYCzmaownPiaGNsjfeubjidkYLmdFOKAHMH2+MHehKDrATcczZkVGqzvrYVV4J2UyWJxsk99irG2kzRqjDVxWaobDObH/ol65mo/Vw6mScbf0ho8tTf/gT", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Date" = _t, SalesAmount = _t]),
ChangedType = Table.TransformColumnTypes (
Source,
{ { "Order Date", type date }, { "SalesAmount", Currency.Type } }
),
InsertYearMonthNumber = Table.AddColumn (
ChangedType,
"Year Month Sequential",
each Date.Year ( [Order Date] ) * 12 - 1 + Date.Month ( [Order Date] ),
Int64.Type
),
GroupedRows = Table.Group (
InsertYearMonthNumber,
{ "Year Month Sequential" },
{ { "SalesAmount", each List.Sum ( [SalesAmount] ), Currency.Type } }
),
Growth = Table.AddColumn (
GroupedRows,
"MoM Growth",
( OuterTable ) =>
let
PrevMonth = List.Max (
Table.SelectRows (
GroupedRows,
( InnerTable ) =>
InnerTable[Year Month Sequential] < OuterTable[Year Month Sequential]
)[Year Month Sequential]
),
PrevMonthSales = List.Sum (
Table.SelectRows ( GroupedRows, each [Year Month Sequential] = PrevMonth )[
SalesAmount
]
),
Difference = OuterTable[SalesAmount] - PrevMonthSales,
Result = Difference / PrevMonthSales
in
Result,
Percentage.Type
),
SortedRows = Table.Sort ( Growth, { { "Year Month Sequential", Order.Ascending } } )
in
SortedRowsHi @Mubar
I’m not sure if I understand you well but if you mean MOM based on YYYMM, below code is my suggestion:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZdBr101DIT/y1v3RrEdx/ayEmJRCViwfOqiVBUbBFVFkfj3zLknTvzY3lFOEmf8ee7r6wvPx/uv3x7ML+9eJHS4tTFfPr57fdHHT5/+vQUaw/qgJvpUSIpEFMyzUdySHSkGK3Pztcjr93iYj9xJqH5PYvr9MX98+P5nrqDJqs3GU2Gpkg3p1nhJdiRzJdYW9zYcZZFoZ5VGdEtQ/liK6bTR27yVWRQUwQxXXReiI5mquzaOrE9ZNH32kQen+j2VKRpZ1Xi8//77rXh06tHGrulWqBsZUfN17lEkckHl2lxFffz65etS5ugS0vq9kRVFcADj1uX+HBWJnbh7k1XWeSQbRl2arOPp45fPf+eVlEkpS4S3OJLwDHwvYlX857/+yZ2mo7ZtrLr2IpFxYKu5tooiibkZHCF5viMN7zRRv7Ge8Icvn1dhrfPEdXdht8IiTOJNLStxJBxuEnaK9N6WyCLCuck6hJdVKjDtSE/AfEeanW1KU1qN9uETbCnPrXQqHEue5ksFRYqw3nQdfRRJ0Wg4uvH98P1Ihkq4tLEc8eOX3+7fPeA9zgL5EajzuDo9C05FGhIuvJ+9fC7QgABHUHb6VnhE6FW7rM9Zo85dt8dBgG950967zzY9j3Ckgav2/Fqy5vk1Q+WsLXCsRn/eVFQ7yjZOZy5lMvOQ7To7CqwFZ0W768lclB59uGf7rWZ+HsBNhm82RFHUaR5ylTWzh5m3bssGz1a+nwfkguDZEFtB33HobMPTplvSYGdrtopzt+ttgnBCI683OAIRk8EeC0B+FA30j6etYbazpoeHoMNvyoy7624FbYynDs822ZJjf+6Nfb3OsxPWWwMm6FTJUh8J1eFro9U/WiQBbS/v+Dr3nh64D/CdTCCuI8fBOrQj5ZWO5DFR8LQiXuhIMImCqcvZdeqghUNAJl51OEqgRGhH1+rFm47wzgU6SnCWIcbhnZtRvusZE9wBErTkWwNdkroBC2nuWWYBG+bbyJ2EqgQKw4+yS36QP8jEqDGvkx94D4wqTCrKIxSso1m0bwz7kRyORIWW73CGrWCCOdAdI4fYQXdXdgD1DPMjTTXeE7vg1NCTYGbfOWMrAj5PkETyYwfBfSIzxCKJFJhihDLMoPmsW4gxJorj21tHIu8T1U6rllWodcQsQ/QsgjKvRMNZnrOIh14lzWxyEIx37XYi1SygBTTh1TYt73pIiznJkTSxg1PDHALM+nzTLTc1r9TS+kijbkUn0oQ1kuTcWcNTBJbjPMBWYDl22aXzQmcMIva0NkB3SIs2uuAY+6pbMotreiYduaC2B6LOJl1VlJFhm3i+wyEqTx3KuaigTkeP62ua+2xlBo4HzHgOla0gYOAIeTYuFHbFRp7pbPXWXdEOZzedb9LUPe5MvG/Wj6PAOCMo53e21rM4CkKf+d0PhK8JOSnnXcHpREwZsUICvaEzI4keovciaQThOjs3HsXUJ0njbcQtYXR3RdNFPunm7JXLZD821/8D06ZfuYdyDBxJkKBB+8WKkvsDMRT/PBYXc4CvVDuuqLIoIr1KFldgW9mLvMR7CM5ZhENmctUrPc9k31mBw2HiyVp08KsE83pex4+AFg106bB021aQ1bRvhlDBfGACYeKy5IkP5QUIAUPGboWzEfzGlP+9YN7CcqQrGG6lKC7ExqEvxi5b4XNn0ewdozpTDJd/Awgq13OzZuUOyyOunlscq4FaAfMxMy1JLxJ4KZ2yDlZScyfD6NqzvUh+zTraxqosDeGnE3Y+f/4PvsMkeB4nHR/4ITAS/hqWkLf2geEQtbfjCshQbkcw/N8f16fCMINk4/NB0ggoM6O+HwEAQdBuPJNiNeMB8zlM2GtgI4QLyWGLihbpig+aVpiFL3NcltMcGVsIUwMR8l8IF1hdA+iaZ5YB60jEiF5ShkaRwEsUIWedFC4iP8jcHCmLYpro5rKWnAmDdMu/B5VwE3+rkmOFSIga1+DulLQ8GOvT4EOc+ON/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Date" = _t, Profit = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Order Date", type date}, {"Profit", Currency.Type}}),
Custom = Table.AddColumn(ChangedType, "YYYYMM", each Date.ToText([Order Date],"yyy-MM")),
Group = Table.Group(Custom, {"YYYYMM"}, {{"Total Profit", each List.Sum([Profit]), Currency.Type}}),
MOM = Table.AddColumn(Group, "MOM Growth", each [Total Profit]/ Table.SelectRows(Group,(x)=> x[YYYYMM]=Date.ToText(Date.AddMonths(Date.From([YYYYMM]&"-01"),-1),"yyyy-MM"))[Total Profit]{0}?-1,Percentage.Type)
in
MOM
but if you mean MOM for each day, solution will be as below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZdBr101DIT/y1v3RrEdx/ayEmJRCViwfOqiVBUbBFVFkfj3zLknTvzY3lFOEmf8ee7r6wvPx/uv3x7ML+9eJHS4tTFfPr57fdHHT5/+vQUaw/qgJvpUSIpEFMyzUdySHSkGK3Pztcjr93iYj9xJqH5PYvr9MX98+P5nrqDJqs3GU2Gpkg3p1nhJdiRzJdYW9zYcZZFoZ5VGdEtQ/liK6bTR27yVWRQUwQxXXReiI5mquzaOrE9ZNH32kQen+j2VKRpZ1Xi8//77rXh06tHGrulWqBsZUfN17lEkckHl2lxFffz65etS5ugS0vq9kRVFcADj1uX+HBWJnbh7k1XWeSQbRl2arOPp45fPf+eVlEkpS4S3OJLwDHwvYlX857/+yZ2mo7ZtrLr2IpFxYKu5tooiibkZHCF5viMN7zRRv7Ge8Icvn1dhrfPEdXdht8IiTOJNLStxJBxuEnaK9N6WyCLCuck6hJdVKjDtSE/AfEeanW1KU1qN9uETbCnPrXQqHEue5ksFRYqw3nQdfRRJ0Wg4uvH98P1Ihkq4tLEc8eOX3+7fPeA9zgL5EajzuDo9C05FGhIuvJ+9fC7QgABHUHb6VnhE6FW7rM9Zo85dt8dBgG950967zzY9j3Ckgav2/Fqy5vk1Q+WsLXCsRn/eVFQ7yjZOZy5lMvOQ7To7CqwFZ0W768lclB59uGf7rWZ+HsBNhm82RFHUaR5ylTWzh5m3bssGz1a+nwfkguDZEFtB33HobMPTplvSYGdrtopzt+ttgnBCI683OAIRk8EeC0B+FA30j6etYbazpoeHoMNvyoy7624FbYynDs822ZJjf+6Nfb3OsxPWWwMm6FTJUh8J1eFro9U/WiQBbS/v+Dr3nh64D/CdTCCuI8fBOrQj5ZWO5DFR8LQiXuhIMImCqcvZdeqghUNAJl51OEqgRGhH1+rFm47wzgU6SnCWIcbhnZtRvusZE9wBErTkWwNdkroBC2nuWWYBG+bbyJ2EqgQKw4+yS36QP8jEqDGvkx94D4wqTCrKIxSso1m0bwz7kRyORIWW73CGrWCCOdAdI4fYQXdXdgD1DPMjTTXeE7vg1NCTYGbfOWMrAj5PkETyYwfBfSIzxCKJFJhihDLMoPmsW4gxJorj21tHIu8T1U6rllWodcQsQ/QsgjKvRMNZnrOIh14lzWxyEIx37XYi1SygBTTh1TYt73pIiznJkTSxg1PDHALM+nzTLTc1r9TS+kijbkUn0oQ1kuTcWcNTBJbjPMBWYDl22aXzQmcMIva0NkB3SIs2uuAY+6pbMotreiYduaC2B6LOJl1VlJFhm3i+wyEqTx3KuaigTkeP62ua+2xlBo4HzHgOla0gYOAIeTYuFHbFRp7pbPXWXdEOZzedb9LUPe5MvG/Wj6PAOCMo53e21rM4CkKf+d0PhK8JOSnnXcHpREwZsUICvaEzI4keovciaQThOjs3HsXUJ0njbcQtYXR3RdNFPunm7JXLZD821/8D06ZfuYdyDBxJkKBB+8WKkvsDMRT/PBYXc4CvVDuuqLIoIr1KFldgW9mLvMR7CM5ZhENmctUrPc9k31mBw2HiyVp08KsE83pex4+AFg106bB021aQ1bRvhlDBfGACYeKy5IkP5QUIAUPGboWzEfzGlP+9YN7CcqQrGG6lKC7ExqEvxi5b4XNn0ewdozpTDJd/Awgq13OzZuUOyyOunlscq4FaAfMxMy1JLxJ4KZ2yDlZScyfD6NqzvUh+zTraxqosDeGnE3Y+f/4PvsMkeB4nHR/4ITAS/hqWkLf2geEQtbfjCshQbkcw/N8f16fCMINk4/NB0ggoM6O+HwEAQdBuPJNiNeMB8zlM2GtgI4QLyWGLihbpig+aVpiFL3NcltMcGVsIUwMR8l8IF1hdA+iaZ5YB60jEiF5ShkaRwEsUIWedFC4iP8jcHCmLYpro5rKWnAmDdMu/B5VwE3+rkmOFSIga1+DulLQ8GOvT4EOc+ON/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Date" = _t, Profit = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Order Date", type date}, {"Profit", Currency.Type}}),
Custom = Table.AddColumn(ChangedType, "YYYYMM", each Date.ToText([Order Date],"yyy-MM")),
Group = Table.Group(Custom, {"Order Date", "YYYYMM"}, {{"Total Profit", each List.Sum([Profit]), Currency.Type}}),
MOM = Table.AddColumn(Group, "MOM Growth", each [Total Profit]/List.Sum(Table.SelectRows(Group,(x)=> x[Order Date]=Date.AddMonths([Order Date],-1))[Total Profit])-1,Percentage.Type)
in
MOM
I hope these two ways help you to work around and find your solution.
1 Like
Hello @Mubar
Did the responses above help solve your query?
If not, can you let us know where you’re stuck and what additional assistance you need?
If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION
Thank you
Hello @Mubar
We’ve noticed that no response was received from you on the post above.
Just following up if the response above help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Mubar
Due to inactivity, we’d like to conclude that you no longer require assistance to solve your inquiry.
If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
Thank you @SoftwareTrain for this solution.
1 Like