Reply
lawada4
Frequent Visitor

how to get the previous nonblank value

i have posted this question twice before but the provided answer did not solve the case.

 

im trying to find the previous day closing price for stocks so that i can calulate day on day change. the problem is stock market is not open everyday and in the days when the market is closed previous day price will be blank.

 

im using the following measure but it's not returning the intended output when i present it in card visual or in scroller custom visual

previous day price1 = CALCULATE(SUM('stocks data'[Close]), OFFSET(-1,ALLSELECTED('stocks data'[Date]),ORDERBY('stocks data'[Date],ASC)))
 
lawada4_0-1671309781291.png

as in the image, the measure works in table visual format but doesnt give last date nonblank value in the card visual.

the expected output in the card visual should be 142.16 instead of 131062.04

 

appreciate the support as this is an urgent matter.

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@lawada4 Try:

DOD Change Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __Price = MAX('Table'[last closing price])
  VAR __PrevDate = MAXX(FILTER(ALL('Table'), [Date] < __Date && [last closing price] <> BLANK(),[Date])
  VAR __PrevPrice = MAXX(FILTER(ALL('Table'), [Date] = __PrevDate), [last closing price])
  VAR __Result = DIVIDE( ( __Price - __PrevPrice ), __Price, 0 )
RETURN
  __Result


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
avatar user
Anonymous
Not applicable

Hi @lawada4 

Can you provide your sample to us so that we can deal with your question better ?

 

Best Regards,
Community Support Team _ Ailsa Tao

Greg_Deckler
Super User
Super User

@lawada4 Try:

DOD Change Measure = 
  VAR __Date = MAX('Table'[Date])
  VAR __Price = MAX('Table'[last closing price])
  VAR __PrevDate = MAXX(FILTER(ALL('Table'), [Date] < __Date && [last closing price] <> BLANK(),[Date])
  VAR __PrevPrice = MAXX(FILTER(ALL('Table'), [Date] = __PrevDate), [last closing price])
  VAR __Result = DIVIDE( ( __Price - __PrevPrice ), __Price, 0 )
RETURN
  __Result


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)