cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jmaguirre7
Frequent Visitor

How to reference the last value a measure produces, within that measure?

I have measure that is a running total across a calendar table.  It adds known future inventory injections and subtracts estimated sales for each day.

I would like it to check if the value of the previous date was less than zero.  That way I can modify the measure to stop subtracting the estimated daily sales until another positive inventory injection is received.

Or maybe there is a better way, I have attached a picture of the table for reference.Inventory Problem.png 

1 ACCEPTED SOLUTION
selimovd
Community Champion
Community Champion

Hello @Jmaguirre7 ,

 

sure, save the MAX date in a variable and subtract one day.

Then in the Calculate statement use your measure and overwrite the date with yesterday's date. If it's smaller than 0 give another result, otherwise your measure:

Running Total yesterday =
VAR vPrevDay = MAX( 'DateTable'[Date] ) - 1
VAR vRunningTotalYesterday =
    CALCULATE(
        [Running Total],
        'Date'[Date] = vPrevDay
    )
RETURN
    IF(
        vRunningTotalYesterday < 0,
        BLANK(),
        [Running Total]
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

2 REPLIES 2
selimovd
Community Champion
Community Champion

Hey @Jmaguirre7 ,

 

did it work with the measure I proposed?

I'm curious if you could solve this issue 🙂

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
selimovd
Community Champion
Community Champion

Hello @Jmaguirre7 ,

 

sure, save the MAX date in a variable and subtract one day.

Then in the Calculate statement use your measure and overwrite the date with yesterday's date. If it's smaller than 0 give another result, otherwise your measure:

Running Total yesterday =
VAR vPrevDay = MAX( 'DateTable'[Date] ) - 1
VAR vRunningTotalYesterday =
    CALCULATE(
        [Running Total],
        'Date'[Date] = vPrevDay
    )
RETURN
    IF(
        vRunningTotalYesterday < 0,
        BLANK(),
        [Running Total]
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors