Hi all,
I want to show a pivot line chart of 3 things:
1) Index values
2) Index % change (from date to date)
3) Accumulative change
I have 2 relevant tables:
1) Date table - DateTBL
2) Indexes table- UnitedIndexesDB
To accomplish bullet #(2), I tried to type a DAX formula, but I am missing something:
Index % change:= (CALCULATE(SUM(UnitedIndexesDB[Value]))/CALCULATE(SUM(UnitedIndexesDB[Value]),FILTER(UnitedIndexesDB,UnitedIndexesDB[Date]<MAX(UnitedIndexesDB[Date]))))-1
Attached is the output (orange line):
When I look at the PP measure I see an answer, but in the chart the value=0, not sure why...
I think that it might be related to that the index doesn't have a value for each day, while the dates data table that is used as the X axis, does have a date for every day. Also, when there is no data it shows 0 instead of ignoring the date.
If you also can help in answering how bullet#3 can be accomplished that will really help.
Thank you