Rolling 3 Month Forecast VAR - Does Not Want SUM of all Months in Total just Latest Month

[JaclynPugh2022]

I have a rolling monthly forecast formula showing monthly totals, but it is summing all the month values together at the total level and I do not want that.  I want it to show total sum of the current month.  What do I have to do to modify the total level value?

Spend Forecast based on last 3 Mo =

VAR Spend1MonthAgo = CALCULATE([Received Spend], DATEADD('DimDate1'[Date],-1, MONTH ) )

VAR Spend2MonthsAgo = CALCULATE([Received Spend], DATEADD('DimDate1'[Date],-2, MONTH ) )

VAR Spend3MonthsAgo = CALCULATE([Received Spend], DATEADD('DimDate1'[Date],-3, MONTH ) )

RETURN

DIVIDE( Spend1MonthAgo + Spend2MonthsAgo + Spend3MonthsAgo, 3 , 0 )

Result:

January: $100

February: $150

March: $100

Total Level: $350  <----This needs to display current month total, not a sum of all months.

Thank you

[amitchandak · Accepted solution]

@JaclynPugh2022 , Try a measure like example

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))

[v-kkf-msft · Accepted solution]

Hi @JaclynPugh2022 ,

Please try the measure.

Spend Forecast based on last 3 Mo = 
CALCULATE (
    [Received Spend],
    DATESINPERIOD (
        DimDate1[Date],
        EOMONTH ( MAX ( 'Table'[Date] ), -1 ),
        -3,
        MONTH
    )
) / 3

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JaclynPugh2022]

Thank you so much!  That works like a charm!!

[amitchandak · Accepted solution]

@JaclynPugh2022 , Try a measure like example

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))

[JaclynPugh2022]

Thank you for the suggestion.  That does result in the current month total (at top of list below) to appear in the overall grand total field (at the bottom); however, I need it to roll forward 3 months as a forecast.  Any suggestions?   Thank you