Extending value of a measure forwards but not backwards

[Anonymous]

Hi,

I have a budget number (which is the summation of a column), and it stops with the most recent value. I want that number to extend to be a "forecasted budget", so I created a measure as such:

Forecasted Budget =

CALCULATE(SUM('Financial Data'[Total Notional Budget]), LASTDATE('Financial Data'[Accuracy Date]))

That date is linked to a calendar table in the background. I only want the numbers going forward from the "last date", but when I graph it the line goes all the way across (as it should)

Thanks for your help.

Vanlang

[amitchandak]

@Anonymous , not very clear.

Try one of the formulae if this can work

CALCULATE(SUM('Financial Data'[Total Notional Budget]), filter(all(Date), Date[Date]>= LASTDATE('Financial Data'[Accuracy Date]))

or

CALCULATE(SUM('Financial Data'[Total Notional Budget]), filter(all(Date), Date[Date]>= LASTDATE('Financial Data'[Accuracy Date]), CROSSFILTER ( 'Financial Data'[Accuracy Date], Date[Date], NONE ))

[Greg_Deckler]

@Anonymous - Maybe something like:

Forecasted Budget =

CALCULATE(SUM('Financial Data'[Total Notional Budget]), MAX(ALL('Financial Data'[Accuracy Date])))

Hard to tell, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. 

[Anonymous]

Sorry, you're right, there is not a lot of information.

I have a table with financial information:

Date	Value
November 1, 2019	5
November 1, 2019	10
October 1, 2019	12
October 1, 2019	13

When I graph the budget on a graph, it shows that for October the value is 25 and for November it is 15.

I don't have any December values. I want to create a measure that will show that the value for the budget is 15 in perpetuity going forward.

I have used the formula:

CALCULATE(SUM(Table[Value]), LASTDATE(Table[Date]))

However the issue is that it creates it as a line going backwards and forwards, so October is also 15, September is 15, but I only want December to be 15, etc etc.

I hope that clarifies things.

Thanks again

[v-yiruan-msft · Accepted solution]

Hi @Anonymous,

First, please create a Date table and a measure as below. Then drag the date field in Date table and new created measure onto the visual:

Forecasted Budget = 
VAR _curval =
    CALCULATE (
        SUM ( 'Financial Data'[Total Notional Budget] ),
        FILTER (
            'Financial Data',
            'Financial Data'[Accuracy Date] = MAX ( 'Date'[Date] )
        )
    )
VAR _maxdate =
    CALCULATE ( MAX ( 'Financial Data'[Accuracy Date] ), ALL ( 'Financial Data' ) )
VAR _foreval =
    CALCULATE (
        SUM ( 'Financial Data'[Total Notional Budget] ),
        FILTER ( ALL ( 'Financial Data' ), 'Financial Data'[Accuracy Date] = _maxdate )
    )
RETURN
    IF ( MAX ( 'Date'[Date] ) <= _maxdate, _curval, _foreval )

Best Regards

Rena

[Anonymous]

Hi @v-yiruan-msft 

I cannot thank you enough for solving this!!! I have tried for days to solve it to no avail!

thank you a million times over!!!