Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

DAX Calculate Sum of Sales + Forecast

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
,financials[Month Number]
, SUM(financials[ Sales]))


This Year Average = // Take Average from this Year as Forecast
VAR CurrentYear = 2014
, Dates[Year] = CurrentYear


Sales Forecast = //Take This Year Average for future months of this year
VAR CurrentMonth = 10
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
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



Frequent Visitor

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])

View solution in original post

Frequent Visitor

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])
Community Champion
Community Champion

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

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)



Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors