Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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)