Measure to calculate cumulative percentage controled by date slicer

[epod184]

Hi,

I have a table called 'prices' with fund data in it, which has the daily buy and sell prices for each fund.

I want to build a chart to track the daily percentage change between the buy price at the start of a date period and the sell price of the fund on each date between the date filters

I have a dates table which I want to use as the report slicer, which will dictate the start date and the end date. The date table has a relation to the prices table via the date field

I have a rough example built in excel shown below. The yellow highlighted cells will be the min and max dates from the date table that is dictaed by a report slicer.

I want the measure to replicate what column E is calculating.

So the end chart would look something like this:

Hopefulyl that makes sense!

Thanks in advance

epod

[martinbordasch]

Hi all, same issue here. I don't have the definitive answer, but what you really need to calculate is the percentual change of each entry in relation to the oldest one. It's the same as the cumulative percentual change, but easier to calculate. If your first entry is fixed, the CALCULATE function along with REMOVEFILTERS should let you call the first entry. The rest is the same as v-huizhn-msft told you.

[v-huizhn-msft]

Hi @epod184,

I try to reproduce your scenario, please follow the following steps.

1. I create DateTable using the formula.

DateTable = CALENDAR(DATE(2017,7,2),DATE(2017,7,25))

2. Create relationship between the DateTable and Table.

3. Create measures to get the start, end date and prices.

Start = MINX(ALL(DateTable),DateTable[Date])
End = MAXX(ALL(DateTable[Date]),DateTable[Date])
start-price = LOOKUPVALUE('Table'[prices_buy],'Table'[price_date],DateTable[Start])
end-price = LOOKUPVALUE('Table'[prices_buy],'Table'[price_date],DateTable[end])

4. Create a calculated column to get the change percentage as what you did in excel.

Change = ('Table'[prices_sell]-'Table'[start-price])/'Table'[prices_sell]

5. Create a new calculated column to filter price_date in during start-end.

New_price_date = IF('Table'[price_date]>=DateTable[Start]&&'Table'[price_date]<=DateTable[End],'Table'[price_date],BLANK())

6.Create a line chart, select the New_price_date as X-axis level, the change as value level, you will get the expected result.



Best Regards,
Angelia

[epod184]

Thanks very much for your help Angelia

I'm not sure I was clear in the information I gave. what you described worked perfecting if I only have one 'prices_sedol', but I have multiple funds.

So when I try and create the change % I get an error

"table of multiple values was supplied when a single value was expected"

I'm guessing it needs another field for the lookup maybe?

[v-huizhn-msft]

Hi @epod184,

Your prices[prices_start_price] returns multiple values, right? You should use one of them.

Best Regards,
Angelia