DAX formula to use previous days total plus some addition to forecast future value.

[FrenchConnectio]

Hi All,

I am working on a poroject that involves using the previous row total plus some amount to forecast a future quantity. Essentially, in the table below, the date column extends a few weeks before and after today's date, which is indexed as 0 in an index column (not shown). The values before today are known, and automatically updated, so the Value column equals the Total column for every date before today. For today and the future, the forecast should be the previous day's total plus the previous day's addition. 

I know how to calculate running totals in PowerBI, but I can't wrap my head around how to structure my DAX query to include an addition to the previous day's total. Any help or advice would be greatly appreciated!

Date Index	Value	Addition	Total
11/5/2020	5		5
11/6/2020	6		6
11/7/2020	7		7
11/8/2020	3		3
11/9/2020	4		4
11/10/2020	5		5
11/11/2020	11		11
11/12/2020	9		9
11/13/2020	11	19	9
11/14/2020	11		28
11/15/2020	11	7	28
11/16/2020	11		35
11/17/2020	11	4	35
11/18/2020	11		39
11/19/2020	11		39
11/20/2020	11		39
11/21/2020	11	22	39
11/22/2020	11	4	61
11/23/2020	11		65

[v-eqin-msft · Accepted solution]

Hi @FrenchConnectio ,

Please try thisa after adding a Index column:

Measure = 
IF ( MAX ( 'Table'[Date Index] ) 
          < TODAY () - 4, 
     MAX ( 'Table'[Value] ), 

IF (
    MAX ( 'Table'[Date Index] )
        = TODAY () - 4,
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] = MAX ( 'Table'[Index] ) - 1 )
    ),
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]
                <= MAX ( 'Table'[Index] ) - 1
                && 'Table'[Date Index]
                    = TODAY () - 5
        )
    )
        + CALCULATE (
            SUM ( 'Table'[Addition] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Index] < MAX ( 'Table'[Index] ) )
        )
)
)

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[FrenchConnectio]

Hi  @mahoneypat  I think the issue is that with that is the Addition column doesn't get incorporated. The Formula I have is: 

Total =

Var Previous_Day_Value = Calculate(SUM(Table1'[Value]),PREVIOUSDAY(Table1'[Date Index]))

Var Previous_Day_Add = Calculate(SUM(Table1'[Addition]),PREVIOUSDAY(Table1'[Date Index]))

Return

IF(‘Table1'[Date Index]<TODAY(),’Table1'[Value],Previous_Day_Value + Previous_Day_Add)

However, the column only fills until today, whereas I need it to go forward until the end of my date range, weeks in the future. Not sure where I am going wrong...

[mahoneypat]

You can do it with variables.

NewMeasure = var prevtotal = <prev total expression>

var prevvalue = <prev value expression>

return prevtotal + prevvalue

Regards,

Pat