Forum Discussion

dm1904's avatar
dm1904
Helper I
7 years ago
Solved

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

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 accros...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

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

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

     

     

    Best Regards,

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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

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

 

 

Best Regards,

  • dm1904's avatar
    dm1904
    Helper I
    7 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      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,