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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
AndreeaV
Advocate I
Advocate I

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!
 
Screenshot 2023-08-07 215523.png

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

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

Jihwan_Kim_0-1691465468375.png

 

 

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

Hi,

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

Jihwan_Kim_0-1691465468375.png

 

 

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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