Calculating MoM, comparing between row month to last month of previous year

Question & Answer
, , ,
1

Hi,
I have a table grouped by MM/YYYY (sample day aggregated by month), and counts day occurrences for each month.
For each row I want to calculate the difference between current row month to last month of previous year.
So for example, for each month in 2023 I will use the measure from 12/2022, to calculate MoM%.

image

I tried using this function:
periodOverPeriodPercentDifference(count({sample_day}), {sample_day}, MONTH, extract('MM',{sample_day}))
with a dynamic offset, but getting an error regarding offset not using a correct type (even though it does return an integer).
I have also tried using periodOverPeriodLastValue, or first getting the related period measure (last month of previous year) for each row using LastValue, but nothing works.
Any idea of how can this be achieved?
Thanks

2

The offset needs to be a hardcoded number. It cannot by dynamic.

I can mark this as a feature request.

Besides that have you looked into this article that outlines I believe what you are trying to do.

3

Thanks
I did look into the article you have sent, but it does not help me achieve my goal.
I was able to create a calculated column showing the date I would like to calc its measure by and compare to, using this formula:
minOver(min({date}), [{date}])
which I altered to this (to get last month previous year):
minOver(min(truncDate('MM',addDateTime(-extract('MM',{sample_day}),'MM',{sample_day}))), [{sample_day}])
Next step would be creating a calculated column which calc the measure for that specific date above (period of last month of previous year).

image

Any idea of how to calculate that missing field?