cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FrenchConnectio
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 IndexValueAdditionTotal
11/5/20205 5
11/6/20206 6
11/7/20207 7
11/8/20203 3
11/9/20204 4
11/10/20205 5
11/11/202011 11
11/12/20209 9
11/13/202011199
11/14/202011 28
11/15/202011728
11/16/202011 35
11/17/202011435
11/18/202011 39
11/19/202011 39
11/20/202011 39
11/21/2020112239
11/22/202011461
11/23/202011 65
1 ACCEPTED SOLUTION
Eyelyn9
Community Support
Community Support

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

11.17.4.1.PNG

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

View solution in original post

3 REPLIES 3
Eyelyn9
Community Support
Community Support

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

11.17.4.1.PNG

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

View solution in original post

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

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
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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