Percentage calculation

[Anonymous]

Hi everyone!

I would love some help with calculating the change in percentage.

In short, my dataset consists of Nike's stock closing price during 2020, the relevant attributes are "Date" and "Close price". In my dashboard, I have tried create a card in combination with a slicer displaying "between" dates. 

What I want to display is basically the change in Nike's stock price from the chosen start date to the end date in the slicer, for example between 3/1-3/7 as seen in the picture. I have tried out different measures but dosen't seem to get it right. How is this done in the easiest way? 

Thanks on forehand!

[mahoneypat · Accepted solution]

Not clear if you have a Date table or not, but the approach is similar.  Basically, you need to harvest the min/max dates selected in your slicer, calculate two variables (one for each), and then do the math.  Here is an example that assumes you have a single table with all your data.

Pct Change =
VAR vStartDate =
    MIN ( Table[Date] )
VAR vEndDate =
    MAX ( Table[Date] )
VAR vStartPrice =
    CALCULATE (
        MIN ( Table[Close] ),
        Table[Date] = vStartDate
    )
VAR vEndPrice =
    CALCULATE (
        MIN ( Table[Close] ),
        Table[Date] = vEndDate
    )
RETURN
    DIVIDE (
        vEndPrice - vStartPrice,
        vStartPrice
    )

Regards,

Pat

[Anonymous]

thank you both @mahoneypat and @amitchandak, it is working now!

[amitchandak · Accepted solution]

@Anonymous , prefer to use a date table

diff between selected range =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = minx(allselected('Date'),'Date'[Date])
return
divide(calculate(max(table[close price]), filter('Date', 'Date'[Date] =_max)),calculate(max(table[close price]), filter('Date', 'Date'[Date] =_min)))-1

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[mahoneypat · Accepted solution]

Not clear if you have a Date table or not, but the approach is similar.  Basically, you need to harvest the min/max dates selected in your slicer, calculate two variables (one for each), and then do the math.  Here is an example that assumes you have a single table with all your data.

Pct Change =
VAR vStartDate =
    MIN ( Table[Date] )
VAR vEndDate =
    MAX ( Table[Date] )
VAR vStartPrice =
    CALCULATE (
        MIN ( Table[Close] ),
        Table[Date] = vStartDate
    )
VAR vEndPrice =
    CALCULATE (
        MIN ( Table[Close] ),
        Table[Date] = vEndDate
    )
RETURN
    DIVIDE (
        vEndPrice - vStartPrice,
        vStartPrice
    )

Regards,

Pat