Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

How to calculate future months

I have a forecast table that has all the forecast amounts from January 2017 - December 2017. However, I want to create a measure that only calculate the future months forecast based off the current m...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Anonymous

     

    Based on your description, you want to calculate total from current row all the way up to Dec 2017 for all dates later than today. It's like a reverse running total. Right?

     

    You need to use the Total from Today() to End Of Year, minus the running total from Today() to End Of Year. Please refer to measure below:

     

    Forecast =
IF (
    MAX ( 'Fact'[Date] ) >= TODAY (),
    CALCULATE (
        SUM ( 'Fact'[Amount] ),
        DATESBETWEEN ( 'Fact'[Date], TODAY (), LASTDATE ( ALL ( 'Fact'[Date] ) ) )
    )
        - CALCULATE (
            SUM ( 'Fact'[Amount] ),
            DATESBETWEEN ( 'Fact'[Date], TODAY (), LASTDATE ( 'Fact'[Date] ) )
        ),
    BLANK ()
)

     

     

    Regards,

Anonymous's avatar
Anonymous
Not applicable
9 years ago

bump

MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 years ago

Hi Anonymous,

 

I made an example and assume you are using the Date table linked to the OPEX_Forecast and add this formula:

 

Future_Month_forecast = CALCULATE (
  CALCULATE(SUM(OPEX_Forecast[Forecast]),OPEX_Forecast[Date]>=TODAY())
)

You can see the result below, by month and in total in a card visual.

 

 

Regards,

MFelix

  • Jotten's avatar
    Jotten
    Frequent Visitor
    8 years ago

    This formula does almost what I want.  However, I would like it to calculate whole months.  For example I want to take actuals from Jan-May and then add the forecast from June -Dec.  This formula is only calculating only couple remaining days in June rather than all of June.  I am hoping this is just a simple fix.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jotten, how did you resolve this?? I have the same problem statement.