I have a data table that looks like this, except that it goes for about 3-4 years and has about 40,000 rows:
| Caseid | MonthYear | call_accepted | total_call_count |
|---|---|---|---|
| 1 | Dec 2023 | 1 | 1 |
| 2 | Dec 2023 | 0 | 1 |
| 3 | Nov 2023 | 1 | 1 |
| 4 | Nov 2023 | 1 | 1 |
| 5 | Nov 2023 | 1 | 1 |
| 6 | Oct 2023 | 0 | 1 |
| 7 | Oct 2023 | 0 | 1 |
| 8 | Oct 2023 | 0 | 1 |
| 9 | Oct 2023 | 1 | 1 |
| 10 | Oct 2023 | 0 | 1 |
I have created the following measures:
[% accepted] = DIVIDE(SUM(table1[call_accepted]), SUM(table1[total_call_count])
[% accepted PM] = Calculate([% accepted], DATEADD(datetable[date], -1, MONTH)
[% MoM change] = [% accepted] - [% accepted PM]
With those I can create a table that looks something like this:
| MonthYear | % MoM change |
|---|---|
| Jan 2024 | 32% |
| Dec 2023 | 1.03% |
| Nov 2023 | 5.3% |
| Oct 2023 | 7% |
| Sep 2023 | 3.1% |
| Aug 2023 | -6% |
| Jul 2023 | 0.5% |
| Jun 2023 | 2.23% |
| May 2023 | 8% |
| Apr 2023 | 10% |
| Mar 2023 | 1.24% |
| Feb 2023 | 5.21% |
| Jan 2023 | -6.7% |
| Dec 2022 | -5.03% |
| Nov 2022 | 7.3% |
| Oct 2022 | 3.2% |
| Sep 2022 | 2.1% |
| Aug 2022 | 1.4% |
| Jul 2022 | 2.5% |
| Jun 2022 | -5.23% |
| May 2022 | 1.9% |
| Apr 2022 | 3.6% |
| Mar 2022 | 4% |
| Feb 2022 | 9% |
| Jan 2022 | 3% |
| Dec 2021 | 3.43% |
| Nov 2021 | -1.3% |
| Oct 2021 | -2.2% |
| Sep 2021 | 1.77% |
| Aug 2021 | -2.11% |
| Jul 2021 | 3.1% |
| Jun 2021 | -7.25% |
| May 2021 | 4.3% |
| Apr 2021 | 3.4% |
| Mar 2021 | 5.1% |
| Feb 2021 | 3.09% |
| Jan 2021 | 56% |
My Goal is to create a measure or column that will calculate the average MoM change for each month,
for example for Oct 2023, it will say Oct 2021 = -2.2%, Oct 2022 = 3.2%, and Oct 2023 = 7%
(-2.2% + 3.2% + 7%)/3 = 2.67%
I want it to do that for each month name, and adjust for how many previous months there are.
The other issue is, if you look at the first and last month on the chart, for Jan 2021, there is no previous month and for Jan 2024 there is no current % accepted, so I am returning a full month, instead of change. I want to exclude the first and last month from the calculation.
I've been working with a lot of different ways to create variables, and even successfully created a calculated column version of the % Accepted measure, but this is the closest I've been able to come to getting what I'm after:
% avg MoM change = AVERAGEX(VALUES('Datetable'[MonthYear]), [% MoM change])
If I only ask for Month name, then it mostly works and looks something like this:
| Month | % avg MoM change |
|---|---|
| Jan | 21.08% |
| Feb | 5.77% |
| Mar | 3.45% |
| Apr | 5.67% |
| May | 4.73% |
| Jun | -3.41% |
| Jul | 2.03% |
| Aug | -2.24% |
| Sep | 2.32% |
| Oct | 2.67% |
| Nov | 3.77% |
| Dec | -0.19 |
The issue is, that if I change Month to MonthYear, then [% avg MoM change] is the same as [% MoM change]
I need to be able to have the running average for each month, because I want to use that number to multiply by various metrics for each month and year and see how the seasonality results compare to the real results and to attempt to forecast into the future.
I also need the calculations for the first and last months to clear out, so their averages are correct, no matter which month is currently the first or last month of the dataset
UPDATE:
Before submitting, Stack overflow helped me find these two responses that put together had potential to solve my problem, but so far I'm still having issues.
I used the first link to create this measure, but I keep getting an error "A column specified in the call to function 'DATESINPERIOD' is not of type DATE. this is not supported" though it's very much formatted like a date and I switch from using the date on the Table1 to the DateTable to try and mitigate any issues with nulls
% real Avg MoM Change =
VAR _currentMonth = MAX('DateTable'[Month Number])
RETURN
CALCULATE([% Accepted MoM change], DATESINPERIOD('DateTable'[Date].[Year],
MIN('DateTable'[Date].[Year]),
(FORMAT(MIN('DateTable'[Date].[Year]),"#") - FORMAT(MAX('DateTable'[Date].[Year]),"#")),
YEAR),
FILTER(VALUES('DateTable'[Month Number]),
'DateTable'[Month Number] = _currentMonth))
UPDATE: I have fixed the year interval and the error, now it's just returning blank cells though:
% real Avg MoM Change =
VAR MinYear = CALCULATE(MIN('Table1'[Year]), ALLEXCEPT('Table1','Table1'[Month Year]))
VAR _currentMonth = MAX('DateTable'[Month Number])
VAR _YrIntervals = MinYear - MAX('Table1'[Year])
VAR _EarliestDate = MIN('Table1'[Created_date])
RETURN
CALCULATE([% Accepted MoM change], DATESINPERIOD('DateTable'[Month], MinYear , _YrIntervals,YEAR), FILTER(VALUES('DateTable'[Month Number]), 'DateTable'[Month Number] = _currentMonth)
See if this works for you:
% avg MoM change =
var thisMonths =
FILTER(
ALL('DateTable'),
'DateTable'[Month Number] = SELECTEDVALUE('DateTable'[Month Number])
)
return
AVERAGEX(
SUMMARIZE(
thisMonths,
'Datetable'[MonthYear],
"MoM", [% MoM change]
),
[MoM]
)