cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
12 REPLIES 12
Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

@Ashish_Mathur thanks for reaching out, find the link below.

https://we.tl/t-HNXMw2bRL8

PrevAmount works for the current year but still have issues with last known value from previous year.

Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Works great, Thanks!

I'm also looking for the EUR/KG value from the same month as the last know value, I tried the same formula but it returns blanks.. do you have a solution for this as well?

Super User III

You are welcome.  If my previous reply helped, please mark it as Answer.  Download the PBI file from here with the solution of your new request

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User IV

@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])

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

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]
Super User IV

@RK91 Where did you place your ALL?

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

@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

Super User IV

Try this:

CALCULATE(LASTNONBLANK(ALL(aterial[MM-YY])

If not post sample data so that we can recreate.

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

@Greg_Deckler below a link to a sample pbix file with similar data. hope this helps..

https://we.tl/t-HNXMw2bRL8

Frequent Visitor

@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

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.