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,

MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 years ago
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's avatar
Anonymous
Not applicable
9 years ago

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