Forum Discussion
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 o...
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.
Greg_Deckler
Community Champion
Community ChampionRK91 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])
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]
- Greg_Deckler
RK91 Where did you place your ALL?
Greg_Deckler I tried replacing allselected and wrapped the complete measure in one.. I know not how it should be, but kinda lost in this one
- Greg_Deckler
Try this:
CALCULATE(LASTNONBLANK(ALL(aterial[MM-YY])
If not post sample data so that we can recreate.