Calculating Difference Between Current and last Values (By Date) and Divide by Date Diff

[dm1904]

Hi, I occasionally spend many hours trying to solve something without success. And on these occasions I have to turn to the good people of the PBI community. Hoping somebody out there has come accross something similar.

Below is a simple table > "Package", "Date", "Actual", "Forecast". I am expecting that where an 'Actual' value is recorded, there will often be a blank corresponding 'Forecast' Value (E.g. Test - 09 May 2018 shown below). When this happens I need to calculate a forecast.

For the first part of this I need a column or measure to calculate a rolling difference, which subtracts the row Forecast value from the forecast value of the last assosciated date. E.g. For the 'Test Package' below it would calculate 30,000 - 10,400 = 19,600.

Then I need to divide that by the date difference of the dates used in that calculation. In this case 18 May 2018 minus 29 April 18 = 19 days.......... 19,600 = 1,032

Where the 'Forecast' value is blank but an 'Actual' value is recorded (Using Test 09 May 2018 as the example) Then the formula would take the 1,032 and multiply it by the Date Diff of the actual and the last Forecast Date (In this case 09 May 2018 minus 29 April 2018 = 10 days.) So 1,032 x 10 = 10,320.

The last part of the formula would add the 10,320 to the last availble forecast value of 10,400 = 20,720. This would be the calculated forecast for the 09 May 2018 and is the result I'm trying to get to.

I hope that makes sense, but I've probably done a terrible job of explaining it. Hope somebody can help. Many thanks

[v-jiascu-msft · Accepted solution]

Hi @dm1904,

I assume the dates are in order. Please download the demo from the attachment. You should reconsider which days to use, 18 or 19.

ForecaseNew =
VAR currentPackage = [Package]
VAR currentDate = [Date]
VAR lastdateHasFC =
    CALCULATE (
        MAX ( Table1[Date] ),
        FILTER (
            Table1,
            Table1[Package] = currentPackage
                && Table1[Date] < currentDate
                && Table1[Forecast] > 0
        )
    )
VAR nextdateHasFC =
    CALCULATE (
        MIN ( Table1[Date] ),
        FILTER (
            Table1,
            Table1[Package] = currentPackage
                && Table1[Date] > currentDate
                && Table1[Forecast] > 0
        )
    )
VAR lastForecast =
    CALCULATE (
        MAX ( Table1[Forecast] ),
        FILTER (
            Table1,
            Table1[Package] = currentPackage
                && Table1[Date] = lastdateHasFC
        )
    )
VAR nextForecast =
    CALCULATE (
        MAX ( Table1[Forecast] ),
        FILTER (
            Table1,
            Table1[Package] = currentPackage
                && Table1[Date] = nextdateHasFC
        )
    )
VAR newForecast =
    lastForecast
        + DIVIDE (
            nextForecast - lastForecast,
            DATEDIFF ( lastdateHasFC, nextdateHasFC, DAY )
        )
            * DATEDIFF ( lastdateHasFC, currentDate, DAY )
RETURN
    IF ( ISBLANK ( [Forecast] ), newForecast, Table1[Forecast] )

Best Regards,

[dm1904]

Hi @v-jiascu-msft. That's incredible, thank you! I have some wider testing to do, but so far this seems to have worked in my model. I need to take this a few steps further but I think you've given me the necessary building blocks. The dates wouldn't necessarily be in order, is that necessary? In any case I'm hoping I can sort into date order via Power Query. The only thing I didn't understand was your comment "You should reconsider which days to use, 18 or 19".

Many thanks again for your help, this saved me a lot more stress.

[v-jiascu-msft]

Hi @dm1904,

1. I should make it more clear. It's 18 days in your first post while it's 19 days returned by the formula. It's easy to adjust.

2. The dates don't need to be in order. But the formula searches the nearest dates. In other words, the formula has an order inside. 

3. Another assumption is one package and one date only has one row.

Any related question, please feel free to post here.

Best Regards,