DAX Calculate Sum of Sales + Forecast

[MyriamW]

Hi community,

I have a table with financial data and a connected date table. I have three requeirements:

1.  Calculate a forecast for future months of this year which shall be the average of the past month of this year. Therefore I have these 4 measures:

YTD Average = // Group Sales by Month
AVERAGEX(
SUMMARIZE(financials
,financials[Month Number]
,"MTDAmount"
, SUM(financials[ Sales]))
,[MTDAmount])

This Year Average = // Take Average from this Year as Forecast
VAR CurrentYear = 2014
RETURN
CALCULATE([YTD Average]
, Dates[Year] = CurrentYear
,DATESYTD(Dates[Date]))

Sales Forecast = //Take This Year Average for future months of this year
VAR CurrentMonth = 10
RETURN
IF.EAGER(
AND(
ISBLANK([Sales Measure])
,SELECTEDVALUE(Dates[Month]) >= CurrentMonth)
,[This Year Average])

Sales + Forecast = // Combine Sales and Forecast
IF.EAGER([Sales Measure]
,[Sales Measure]
,[Sales Forecast])

2. Calculate the sum of Sales up to the current month

YTD Sum =
VAR CurrentMonth = 10
RETURN
TOTALYTD([Sales Measure]
, 'Dates'[Date]
, 'Dates'[Month] <= CurrentMonth)​

3. Calculate the sum of Sales including the forecast for future months.

Here I need help. I thought SUMX would do the job adding up all values of my Sales + Forecast Measure. But it does not work like I expected.

Sales + Forecast Sum =
SUMX(Dates, [Sales + Forecast])

I want my Sales + Forecast Sum to just add up the red circled values. How can I archieve this?

DAX still is so confusing to me.. I would also appreciate any suggestions of improving my DAX code!

Thanks in advance!

Cheers, Myriam

[MyriamW · Accepted solution]

Okay I think I found out myself. Dunno if thats the "beste" solution, but this is working in my case:

Sales + Forecast Sum =   

     SUMX(VALUES(Dates[Month]), [Sales + Forecast])

[littlemojopuppy]

@MyriamW I don't mean to offend, but you're doing this all wrong.  Most of all, you're hard coding values into variables which means you're going to need to update this every month.

Could you please supply some sample data?

[MyriamW]

Hi @littlemojopuppy , thanks for your reply.

I should have said that of course in my real report I am not hardcoding any values. I do have variables with MONTH(TODAY()) etc. But as the sample data (I just used some from MS) is not actual data that wouldn't work, and for time reasons I just hardcoded to display my issue.

You can find my sample pbix here: SampleCalculateForecast.pbix

(Hope it is working, I am not able attach the file directly)