Calculate variance based on 1st and last date of time slicer

[PBI5851]

Hello,

 In my dashboard, i have a time series graph which has a set values for each day. The Date is part of the calendar [Date] and the data value is [PurCount] from table [Purchases]

Ex: 1/1/15 has a data value of 200

1/2/15 has a data value of 231

1/3/15 has a data value of 150

1/4/15 has a data value of 124

1/5/15 has a data value of  250

1/6/15 has a data value of 259

and so on for every single day up to current date. 

I do have a time slicer which allows the user to view the data as per their specific timeline. The request however, is to calculate the variance. But this is not a variance like a %SalesYear over year or SamePeriodLastYear as the variance i need to calculate is the 1st day of the time slicer with the last day of the time slicer 

For ex: If a user selects 1/3/2015 to 1/6/2015, the variance should be (259-150)/259 = 42%

To make matters complicated, on one page where the variance is needed, i dont have a range but dropdowns of Year, Quarter and Month. 

is it possible to achieve this ? If yes, please advise on how. 

[parry2k]

@PBI5851 try following measure

Change = 
VAR __firstDate = MIN ( DateTable[Date] )
VAR __lastDate = MIN ( DateTable[Date] )
VAR __firstValue = CALCULATE ( SUM ( Table[Value] ), DateTable[Date] = __firstDate )
VAR __lastValue = CALCULATE ( SUM ( Table[Value] ), DateTable[Date] = __lastDate )
RETURN
DIVIDE ( __lastValue - __firstValue, __lastValue )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[PBI5851]

Hi @parry2k ,

 Not sure what is causing the issue. I am not getting the value as expected, so I started to break down the formula. 

I get the value at min date correctly, but when i use the max date, i get the last value of the Calendar table, even though on the page only 2018 has been selected, which should be 12/31/18 in this case, but it gives me 12/31/2030 (which is the last date in Calendar table)

Max Date Filter = max(Datetable[Date])