Hi there,
Yes, you can modify your month-on-month percentage change measure to exclude the first and latest rows from the calculation. To achieve this, you can use the following DAX measure:
MoM_Percentage_Change =
VAR CurrentDate = MAX('YourTable'[Date])
VAR PreviousDate = CALCULATE(MAX('YourTable'[Date]), FILTER(ALL('YourTable'), 'YourTable'[Date] < CurrentDate))
VAR CurrentValue = CALCULATE(SUM('YourTable'[Value]), FILTER(ALL('YourTable'), 'YourTable'[Date] = CurrentDate))
VAR PreviousValue = CALCULATE(SUM('YourTable'[Value]), FILTER(ALL('YourTable'), 'YourTable'[Date] = PreviousDate))
VAR LatestDate = MAXX(ALL('YourTable'), 'YourTable'[Date])
VAR EarliestDate = MINX(ALL('YourTable'), 'YourTable'[Date])
RETURN
IF(
CurrentDate = LatestDate || CurrentDate = EarliestDate,
BLANK(),
DIVIDE(CurrentValue - PreviousValue, PreviousValue)
)
Replace ‘YourTable’ with the name of your table containing the data.
This measure calculates the percentage change by first determining the current and previous dates as well as the current and previous values. It then checks whether the current date is the latest or earliest date in the dataset. If it is, the measure returns BLANK(), effectively excluding those rows from the calculation. Otherwise, it calculates the percentage change using the current and previous values.
By using this measure, the first and latest rows should be excluded from the month-on-month percentage change calculation, which should result in a more accurate standard deviation value.