cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RK91
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.02last value in July needs to be 1.02

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

RK91_1-1620159457034.png

 

 

 

 

 

1 ACCEPTED SOLUTION

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User III
Super User III

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hi, @Ashish_Mathur 

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?

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
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User IV
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!




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

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




@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

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!




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

https://we.tl/t-HNXMw2bRL8

 

 

@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		

 

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors