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.
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
Community Champion
Community ChampionRK91 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.
Greg_Deckler didn't work, see the sample data below
MM-YY Material Category EUR/UNIT Volume 01/12/2019 A material A category € 1,040 50.000 01/01/2020 A material A category 01/02/2020 A material A category 01/03/2020 A material A category € 1,023 50.000 01/04/2020 A material A category 01/05/2020 A material A category 01/06/2020 A material A category 01/07/2020 A material A category € 1,006 100.000 01/08/2020 A material A category