cancel
Showing results for
Did you mean:
Regular Visitor

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

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
1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Regular Visitor

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]))

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...

Super User IV

You can do it with variables.

NewMeasure = var prevtotal = <prev total expression>

var prevvalue = <prev value expression>

return prevtotal + prevvalue

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

@mahoneypa HoosierBI on YouTube

Announcements

#### Welcome to the User Group Public Preview

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

#### Microsoft Business Applications Summit sessions

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

#### 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