Forum Discussion
Finding Previous Value within a Date Range (Slicer)
Super Usermadkow I did a similar video but the measure that I used in the video needs tweaking to meet your requirement, here is the tweaked measure, and here is the link to the video. Comparing sales with previous day sales should be easy - Power BI - YouTube
Prev Visible Date Sales based on selection =
VAR __startDate = CALCULATE( MIN ( 'Calendar'[Date] ), ALLSELECTED ( ) )
VAR __transactionDate = MAX ( 'Calendar'[Date] )
VAR __prevVisibleDateSales =
IF (
NOT ISBLANK ( [Total Sales] ),
VAR __prevVisibleDate = CALCULATE ( MAX ( 'Table'[Date] ), 'Calendar'[Date] >= __startDate, 'Calendar'[Date] < __transactionDate )
RETURN CALCULATE ( [Total Sales], 'Calendar'[Date] = __prevVisibleDate ) )
RETURN __prevVisibleDateSales
✨ Follow us on LinkedIn and to our YouTube channel
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to 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.
parry2k Thank you!
Slight modification but this seems to work perfectly. I dont' think I need the NOT ISBLANK due to the nature of the data:C Price Diff = VAR _startdate = CALCULATE(min(Data_Table[Valid From]),ALLSELECTED()) VAR _currentdate = MAX(Data_Table[Valid From]) var _PriceDiff = _startdate,Data_Table[Valid From Prev] < _currentdate) CALCULATE(SUM(Data_Table[Price Diff]), Data_Table[Valid From Prev] >= _startdate, Data_Table[Valid From Prev] < _currentdate) RETURN _PriceDiffI start with this, the full data:
Notice I also I have Valid From Prev, this is also in the table along with Prev Price. But the Price Diff column should be 0, there's no price before that.
The C Price Diff (measure) will check if there's something truly before it based on Valid From Prev field. So we get the correct result, a blank was much nicer to read in this case.If I filter down to only show anything from 4/1/2021 and after I get this:
Notice now the C Price Diff is the true value, it's only a $10 difference from 4/1 to 6/22 not a $20 difference.
I'm guessing if you don't have the previous price/date you could use Earlier or similiar methods to get those values.
In this case I was able to write the SQL query and use LAG to get the previous values.Thank you again!
