Forum Discussion

AndreeaV's avatar
AndreeaV
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Get the value from the date before the blank value

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!
 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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 )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    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 )