Skip to main content
cancel
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

Reply
MyriamW
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
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])
MyriamW_0-1697794594119.png

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

 

 

1 ACCEPTED SOLUTION
MyriamW
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

3 REPLIES 3
MyriamW
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])
littlemojopuppy
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

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

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