Hello Everyone,
Hope you all are doing well and are getting ready for the holiday's! I've come across an issue that I have been trying to program for the past day or two, and just cannot wrap my head around it. Basically, I am trying to create a month-over-month and year-over-year pivot table, but do not want to use my existing (Below) function due to this not being 100% accurate.
I've tried using Lookup and conditional Sums - hopefully it is just a syntax error. My table looks like this:
| Month |
Previous Month |
Quantity (=sum(DailyQuantity) |
MoM (WANT TO ADD IN TABLE) |
| Dec 2021 |
Nov 2021 |
55 |
189% |
| Nov 2021 |
Oct 2021 |
29 |
64% |
| Oct 2021 |
Sep 2921 |
45 |
-
|
To calculate Month-over-Month, I want to use the Quantity of Dec 2021 and divide by the Quantity of Nov 2021 (55/29=189%)
I have tried a few set expressions:
- =sum({<'Month'={'=addmonths([Month],-1)'}>}[Quantity])
- Results the same quantity as original month
- =Sum({$<[Month]={"$(=[Previous Month]"},[Previous Month]=>}[Quantity])
- Returns null
Not sure where to go next. Unfortunately using the "Above" and "Below" functions just won't work in our use case. Do y'all have any other ideas that I can try looking at?
Thanks and have a great day!
-Cannon
