Using Run Rate to Forecast Performance

[dw700d]

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 will take us to spend 1,000,000 dollars based on our current averages. For example on Day 7 I subtract the Sum of total inflows(5,500) from the Beginning balance (1,000,000). I then divide that total (994,500) by the average(785) cash outflow from days 1 -7. That tells me, at the current rate we will spend the money in 1,267 days.

How can I use this run rate to calculate how many days it will take me to spend a $1,000,000 by using DAX? 

[Anonymous]

Hi @dw700d ,

You can create a calculated column as below:

Days Until Money is Spent = 
VAR _dayNum =
    RIGHT ( 'Table'[Day ], LEN ( 'Table'[Day ] ) - 4 )
VAR _culCash =
    CALCULATE (
        SUM ( 'Table'[Cash Flow] ),
        FILTER (
            ALL ( 'Table' ),
            RIGHT ( 'Table'[Day ], LEN ( 'Table'[Day ] ) - 4 ) <= _dayNum
        )
    )
VAR _aveCash =
    DIVIDE ( _culCash, _dayNum )
RETURN
    DIVIDE ( 1000000 - ABS( _culCash), ABS ( _aveCash ) )

Best Regards

Rena

[Ashish_Mathur]

Hi,

It will depend upon how the data is arranged.  Share the link from where i can download your PBI file.

[edhans · Accepted solution]

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

[dw700d]

Thanks for the help... Ill put in a word for yo 😂...

[edhans]

Thanks @dw700d - glad I was able to help out.

[dw700d]

@edhans I am trying to calculate the count of days  between todays date  and 2/16/23. I would like that count to update each day. Any thoughts on how I can accomplish that?

[edhans]

This will do that.

Measure = 
    DATEDIFF(
        TODAY(),
        DATE(2023,2,16),
        DAY
    )

Today it shows 941 days. 

[lbendlin]

Create a line at $1M/944 = 1059  and then calculate the average of all your daily values to see if you are above or below 1059