Measure calculating change as of "x" date

[Anonymous]

I have date column, a value column (stock), and I want to calculate the change in Stock as of a certain date.
For example, if the certain date is 21 Jan 2020, I would like to have a measure that allows me to see how much "stock" changes each day, relative to 21 Jan 2020.

I tried using the formula:

Change as of = 
VAR PriceAsOf = CALCULATE(SUM(Query1[stock]), YEAR(Query1[value_date]) = 2020 && MONTH(Query1[value_date]) = 1 && DAY(Query1[value_date]) = 21)

Return
SUM(Query1[stock]) - PriceAsOf

However, there seems to be an issue. It seems that it only manages to get the value for 21 Jan 2020 correct, which is 0. But it didnt work for the other dates after 21 Jan 2020.

Essentially, I would like the measure to perform (SUM(stock) @ whichever date) - (SUM(stock) @ 21 Jan 2020)

Thanks for your help guys!

[amitchandak]

@Anonymous , Not very clear

You  can try like for two dates

Change as of =
VAR PriceAsOf = CALCULATE(SUM(Query1[stock]), Query1[value_date] = date(2020,01,21))
VAR PriceAsOf1 = CALCULATE(SUM(Query1[stock]), Query1[value_date] = date(2020,01,22))
Return
PriceAsOf1 - PriceAsOf

You can try like for two dates from slicer . I assumed indepedent date slicer and taking start and end date of range
Change as of =
var _min = maxx(allselcted(date), date[date])
var _max = maxx(allselcted(date), date[date])
VAR PriceAsOf = CALCULATE(SUM(Query1[stock]), Query1[value_date] = _min)
VAR PriceAsOf1 = CALCULATE(SUM(Query1[stock]), Query1[value_date] = _max)
Return
PriceAsOf1 - PriceAsOf

[Anonymous]

Hi @amitchandak 

Ok to put it simply for example:

I essentially would like a measure that is able to perform what the column "Stock Change as of 2 Jan 2020" does.
Take Stock at whichever date, minus, stock at a certain date (e.g. 2 Jan 2020).

Is that clearer?

Thank you!

[amitchandak · Accepted solution]

@Anonymous , your formula should work as a measure

Change as of =
VAR PriceAsOf = CALCULATE(SUM(Query1[stock]), Query1[value_date] = date(2020,01,02))
Return
SUM(Query1[stock]) - PriceAsOf

or with date slicer

Change as of =
var _min = maxx(allselcted(date), date[date])
VAR PriceAsOf1 = CALCULATE(SUM(Query1[stock]), Query1[value_date] = _min)
Return
SUM(Query1[stock]) - PriceAsOf

Can you share a  sample pbix after removing sensitive data.