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,

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.