+/-100% in Month to month percentage change

Power BI
1

Hello everyone,

I’m trying to build a dashboard to help with entry and exits for forex and stock trading. A test file is in the attachment. However, there seems to be a problem with the Month-on-Month percentage change calculation that I have going on in a measure. In the screenshot you can see the lowest value, which is the newest value available, has a % change of -100%. This (and probably a start value of +100%) results in a standard deviation being way higher than it should be (9.xx% rather than 2.xx%).

As I am unable to post in the DAX section of this forum, I’m posting it here to see whether anyone has an answer.

My question: is there a way to exclude say the first and latest row in the calculation for monthly
Major_FX.pbix (612.0 KB)
percentage change?

Naamloos
Naamloos1737×989 113 KB

2

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.

3

Thank you very much for your detailed reply! Another question that comes to mind when reading your DAX though:

Do I have to put in ‘YourTable’[Date] only or do I have to insert .[Date] behind it as well?
At first my STD function wouldn’t work because I forgot to include .[Date]

4

Bumping this post for more visibility from our experts and users.

5

Hi @BerryDoppenberg

Due to inactivity, a response on this post has been tagged as “Solution”.

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.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!