Annual Percentage Change in Power BI

0
votes

I am looking to calculate an annual percentage change, from today of a number of stock market indices. The data is a flat structure un-pivoted about the date column.

I am using a calendar with daily values extending to the end of the current year. My current code:

Ann pch = 

VAR
__EarliestValue = CALCULATE(SUM('Equity Markets (2)'[Value]),
           SAMEPERIODLASTYEAR( 'Calendar'[Date]))

VAR __LastDateValue = SUM('Equity Markets (2)'[Value])

RETURN
CALCULATE(
    DIVIDE(__LastDateValue,__EarliestValue)-1)

returns answers which are clearly incorrect.

I am very much a DAX/PBI novice.

DATA:

data

I also have a Calendar table:

calendar

structure

1
powerbidaxpercentage
Please share sample data and expected results, moreover are you using a date period slicer for filtering data? If you don't then the second variable will be calculating all time sum instead of for a specific period. – Pratik Bhavsar
Thanks, I included this. I'm really struggling with DAX: could you please provide me a code sample which will refer to an index value at a specific date? – Tikhon

1 Answers

1
votes

I think that the metric is actually correct, what makes you believe it is not? Can you share your output?

What you need to do now is to put it into a context, ie put 'Calendar[Date]' into the desired visual (table for instance). If you don't do so, you'll calculate the total amount of value divided by the total amount of value for dates prior to one year, which might return quite a weird number. If you do so, on the other hand, you should get your YoY comparison.

Something to note - there are also quick measures: quick measures

Check them out, as one of them is actually YoY total, which you might want to try.