Get the value from the date before the blank value

[AndreeaV]

Hi,

In this table ('Random prices'), I use the 'Previous month' measure and I would like to get the price from Jan 3, which is blank. It gives me the price from Jan 5, but I want to give me the price from Jan 2.

The measures I use are: 

Latest price = CALCULATE(SUM('Random prices'[Close price]),'Random prices'[Date]=MAX('Random prices'[Date]))

Previous month = CALCULATE([Latest price], DATEADD('Random prices'[Date], -1, MONTH))

Thanks!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try something like below.

Previous month =
VAR _latestdate =
    MAX ( 'Random prices'[Date] )
VAR _prevmonthlatestdate =
    MAXX (
        FILTER (
            'Random prices',
            'Random prices'[Date] <= EOMONTH ( _latestdate, -1 )
                && DAY ( 'Random prices'[Date] ) <= DAY ( _latestdate )
        ),
        'Random prices'[Date]
    )
RETURN
    CALCULATE ( [Latest price], 'Random prices'[Date] = _prevmonthlatestdate )