Forum Discussion

dw700d's avatar
dw700d
Post Patron
6 years ago
Solved

Using Run Rate to Forecast Performance

I am on a project and we must spend $1,000,000 in 944 days. I am trying to create a tracker that highlights if we are on pace to accomplish this.  In the below example I calculate how many days it wi...
  • edhans's avatar
    6 years ago

    I can make it easy for you. DM me and I'll give you my bank account and you can send me the balance of the money. ğŸ˜‚

    Seriously, try this:

     

    VAR varCurrentDay =
        MAX( 'Table'[Day] )
    VAR varAverageSpend =
        CALCULATE(
            AVERAGE( 'Table'[Cash Flow] ),
            FILTER(
                ALL( 'Table'[Day] ),
                'Table'[Day] > 0
                    && 'Table'[Day] <= varCurrentDay
            )
        ) * -1
    VAR varRemainingSpend =
        CALCULATE(
            SUM( 'Table'[Cash Flow] ),
            FILTER(
                ALL( 'Table'[Day] ),
                'Table'[Day] <= varCurrentDay
            )
        )
    VAR Result =
        DIVIDE(
            varRemainingSpend,
            varAverageSpend,
            0
        )
    RETURN
        Result

     

    It returns this visual

    My PBIX file is here if you want to see what I did. I made the Day count an integer as I needed to use that to filter the data. You can see in the measure that each variable is fairly simple in what it does - one gets the current day, the next the average through that day, then the total spend, then finally it does the division.

     

    If you need further help, see links below to provide data. We cannot use screenshots for data. Great for expected results, but we have to retype stuff for source data.

     

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum