Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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/

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

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.