Forum Discussion

madkow's avatar
madkow
Frequent Visitor
4 years ago

Finding Previous Value within a Date Range (Slicer)

Hi All,

I've been struggling with this for a quite a while and not sure if it can actually be done.  Or I haven't found the right solution.

Basically I want to find an earlier value but it seems the Earlier function will go outside the date range that is provided.

Perhaps there is another way.

Just to note I also have the previous date and values (price) within my table but was having some difficulty fitting the requirements.

Basically the price should "reset" if it's not in the date range.

 

Here is what it looks like with the date range wide open

 

Notice that on the 12/1/2020 date we don't want a Price Diff, its the first record of price history so the Price Diff should be zero.
With an earlier function i can set that price diff to zero if the earlier date is empty, which only happens on the first record.

 

This is what is meant by resetting the price:
In this case the 4/22/2021 date I want the Previous Price to be empty/blank.  I don't want to calculate a difference there.  And If i could get the earlier function to return a blank instead of 2/22/2021, the previous row, I could ensure the price diff is zero.

I've tried several different solutions but none seem to fit the requirement of resetting the price difference by the slicer.

When I use Earlier or a manual "earlier" column or measure it always finds the previous value outside of the slicer range, which from what I understand is how it should work.  But I need something that will  do the same but work within the slicer dates.

The goal is to have multiple years of history but to look at how the price changed within a certain time frame, not including anything outsie of the slicer dates.
Just to note this is a simple example.  There can be multiple parts per supplier and multiple suppliers for parts.

 

Thank you and let me know if you have any questions.

 

Need Help

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    madkow 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.

    • madkow's avatar
      madkow
      Frequent Visitor
      4 years ago

      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 _PriceDiff

       

      I 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!

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    4 years ago

    madkow This is awesome, glad you have a solution in place. Cheers!!

     

     

    ✨ 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.