How to calculate future months

[Anonymous]

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 month (e.g. July 2017 -> December 2017). This is what I have so far, but it's giving me the total forecast (i.e. January -> December 2017). Any ideas on how to fix this?

CALCULATE (
  SUM ( OPEX_Forecast[Forecast] ),
  FILTER (
    ALL ( 'Date'[Date] ),
    MONTH(TODAY() <= MAX ( 'Date'[Date] )
  )
))

[Anonymous]

bump

[v-sihou-msft · Accepted solution]

@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,

[MFelix]

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]

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]

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

[MFelix]

Hi @Anonymous,

In your measure you are comparing the month of the dates that are lower than today of today (6) date this will give you the always a lower number in month than in date you probably need to change it to.somwthing like this

CALCULATE (
SUM ( OPEX_Forecast[Forecast] ),
FILTER (
ALL ( 'Date'[Date] ),
TODAY() <= MAX ( 'Date'[Date] )

))
Just took.month out not on.computer but this.may work.

Regards,
MFelix

[Anonymous]

Thanks, MFelix. However, it's still showing the sum of all the months instead of all the months starting from July.