percent change formula... please help to correct my approach

[Anonymous]

hi all,

below is sample data and my formula which is showing wrong figures, required to calculate country wise percent change for every row (show in Table visual) than sum country wise to show in another graph visual.
here is a screenshot (on left) which shows a row with 0.12 without country name when created after sum of pct change.

sample data:
date         country         dollar_price   Index
4/1/2000 Argentina                    2.5         1
4/1/2001 Argentina                    2.5         2
4/1/2002 Argentina    0.798722045         3
4/1/2003 Argentina    1.423611111         4
5/1/2004 Argentina    1.477966102         5
6/1/2005 Argentina    1.639627201         6
4/1/2000 Australia      1.541666667         7
4/1/2001 Australia      1.515151515         8
4/1/2002 Australia      1.612903226         9
4/1/2003 Australia      1.863354037       10
5/1/2004 Australia      2.272727273       11
6/1/2005 Brazil           2.393703343       12
1/1/2006 Brazil           2.741543224       13
5/1/2006 Brazil           2.777175092       14
1/1/2007 Brazil           2.999765643       15
6/1/2007 Brazil           3.606900157       16
6/1/2008 Brazil           4.733055661       17

code (measure within corresponding table):
Pct Change =
VAR CurrentValue = CALCULATE(VALUES('Big Mac Index2'[dollar_price]))
VAR PreviousValue =
CALCULATE( MAX('Big Mac Index2'[dollar_price]), FILTER('Big Mac Index2', 'Big Mac Index2'[Index]=EARLIER('Big Mac Index2'[Index])-1) )
RETURN
DIVIDE(
CurrentValue - PreviousValue,
PreviousValue
)

note: can't format the code 

screenshot:

regards

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

There is nothing wrong with your formula.

Please check that your Countries table contains all the country names in the Big Mac Index2 table. I see that your country2 column contains "Britain", but the Country Name column doesn't, which may be the cause of the row with 0.12. If the country name is missing, a situation like the following occurs.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@Anonymous 

In your formula, I see "Country 2" field but you are using Country name in the visual. Use only one column.

[Anonymous]

@Fowmy 

nop, please you can see in below screenshot in which i have marked pct change column there is country2 in visual too.

[Fowmy]

@Anonymous 

You the following meaure:

Pcnt Change = 
var __currentcountry  = MAX('Big Mac Index2'[Country])
var __currentdate = MAX('Big Mac Index2'[Date])
var __currvalue = CALCULATE(MAX('Big Mac Index2'[Dollar_Price]), 'Big Mac Index2'[Date] =__currentdate)
var __previousdate = 
    MAXX( 
        FILTER(
            ALL('Big Mac Index2'),
            'Big Mac Index2'[Country] = __currentcountry &&'Big Mac Index2'[Date] < __currentdate 
        ),
        'Big Mac Index2'[Date]
    )
var __prevalue = CALCULATE( MAX('Big Mac Index2'[Dollar_Price] ) , 'Big Mac Index2'[Country] = __currentcountry , 'Big Mac Index2'[Date] = __previousdate )
return
IF(
    __prevalue <> BLANK() ,__currvalue - __prevalue
)
     

[Anonymous]

thanks @Fowmy for the code, i have implemented it but don't know why its not working for me, below are 2 screenshot, first one is for code i copy/paste and changed only column names where needed, 2nd screenshot showing result which is blank pct change.

the code:

the result:

regards

[Fowmy]

@Anonymous 

You have not added the date in the visual, you can try this measure, I added ALL( BIGMAC...)  in the last calculate function

Pcnt Change = 
var __currentcountry  = MAX('Big Mac Index2'[Country])
var __currentdate = MAX('Big Mac Index2'[Date])
var __currvalue = CALCULATE(MAX('Big Mac Index2'[Dollar_Price]), 'Big Mac Index2'[Date] =__currentdate)
var __previousdate = 
    MAXX( 
        FILTER(
            ALL('Big Mac Index2'),
            'Big Mac Index2'[Country] = __currentcountry &&'Big Mac Index2'[Date] < __currentdate 
        ),
        'Big Mac Index2'[Date]
    )
var __prevalue = CALCULATE( MAX('Big Mac Index2'[Dollar_Price] ) , 'Big Mac Index2'[Country] = __currentcountry , 'Big Mac Index2'[Date] = __previousdate , ALL('Big Mac Index2') )
return
IF(
    __prevalue <> BLANK() ,__currvalue - __prevalue
)