Forum Discussion

RK91's avatar
RK91
Frequent Visitor
5 years ago
Solved

Previous value based on date

Hi,

 

I'm struggeling to get the previous value based on the last known price.

My goal is to calculate " this months" volume with last known price before that specific month.

I've tried several options as lastnonblank etc but nothing seems to work yet.

 

last value in July needs to be 1.02

" last value"  in July needs to be 1.02 as shown below

 

 

 

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    You may download my PBI file from here.  See the last field dragged to the measure.  For March, you will see the value as 0.5720 (the last known weighted average price of the previous year).

    Hope this helps.

12 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    RK91 You are going to need some kind of date or numeric month number field. Worst possible case, create a column like this:

    Month Number = SWITCH([Month],
 "January",1,
 "February",2,
 "March",3,
 "April",4,
...

)

    Then you can do this:

    Last Value Column =
  VAR __Table = FILTER(ALL('Table'),NOT(ISBLANK('Table'[last value])) && [Month Number] < EARLIER([Month Number])
  VAR __LastMonthKnown = MAXX(__Table,[Month Number])
RETURN
  MAXX(__Table,[Month Number] = __LastMonthKnown),[last value])

Last Value Measure =
  VAR __CurrentMonth = MAX('Table'[Month Number])
  VAR __Table = FILTER(ALL('Table'),NOT(ISBLANK('Table'[last value])) && [Month Number] < _CurrentMonth
  VAR __LastMonthKnown = MAXX(__Table,[Month Number])
RETURN
  MAXX(__Table,[Month Number] = __LastMonthKnown),[last value])

     

    • RK91's avatar
      RK91
      Frequent Visitor
      5 years ago

      Greg_Deckler 

      Thanks for reaching out, I got there using:

      PrevAmount =
      VAR _selDate = SELECTEDVALUE(Material[MM-YY])
      VAR _maxDate = CALCULATE(LASTNONBLANK(Material[MM-YY], CALCULATE(SUM(Material[EUR/UNIT]))), FILTER(ALLSELECTED(Material), [MM-YY] < _selDate))
      RETURN
      IF(NOT(ISBLANK(SUM(Material[EUR/UNIT]))),
      CALCULATE(SUM(Material[EUR/UNIT]), FILTER(ALLSELECTED(Material),Material[MM-YY]= _maxDate)), BLANK())
       
      however I'm missing the last value from the previous year, I tried "ALL" function but doesn't seem to help.
      any suggestions? There is a year filter on the visual Collumn: 'Calendar' [date]
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        5 years ago

        Hi,

        You may download my PBI file from here.  See the last field dragged to the measure.  For March, you will see the value as 0.5720 (the last known weighted average price of the previous year).

        Hope this helps.