cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
agustinmisaza12
Regular Visitor

DAX FORMULA DOESN'T SUMS

Hello,

 

I'm trying to use this formula to know the total income of a project, based on my actual sales and my forecast sales.

I’m using this formula DAX formula to have the forecast of my income from today on:

FORECAST INCOME=

var Lastdate = MAXX(ALL(Sales[Date]), Sales[Date])

return

IF(SELECTEDVALUE(Calendar[Date]) > Lastdate, SUMX(FORECAST,FORECAST[income]), BLANK())

 

When I do this, I get a table, but my problem is that it doesn’t adds up the total.

 

Sin título.png

 

ANY SOLUTION??

1 ACCEPTED SOLUTION
v-frfei-msft
Community Support
Community Support

Hi @agustinmisaza12 ,

 

To create another new measure based on FORECAST INCOME should work.

Measure = SUMX(FORECAST,[FORECAST INCOME])

 

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

Hi @agustinmisaza12 ,

 

To create another new measure based on FORECAST INCOME should work.

Measure = SUMX(FORECAST,[FORECAST INCOME])

 

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Still doesn´t work. here is the example tables:

FORECAST TABLE:

CCPROJECT FORECAST DATE
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                                     -1/10/2018
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO              3,546,447,5071/11/2018
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 752,277,5601/12/2018
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 752,277,5601/01/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 752,277,5601/02/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 752,277,5601/03/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 752,277,5601/04/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 759,459,0481/05/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 752,277,5601/06/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 752,277,5601/07/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 752,277,5601/08/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO              2,123,402,4731/09/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                 752,277,5601/10/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO              1,371,125,7121/11/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO              1,083,333,1771/12/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO              1,083,333,1771/01/2020
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO              1,083,333,1771/02/2020
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO              1,083,333,1771/03/2020

 

SALES TABLE:

CCPROJECT SALES Date
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                         -1/11/2018
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO  3,546,447,5078/11/2018
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,7603/12/2018
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,7598/01/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,7596/02/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,7598/03/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO                         -3/04/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,7595/04/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO         7,182,2893/05/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,7596/05/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,7595/06/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,7595/07/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,75915/08/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,75912/09/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO  1,371,125,71420/09/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO-      16,473,43516/10/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO     752,276,75521/10/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO-           636,0735/11/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO-        1,497,40819/11/2019
OE699CENTRO COMERCIAL PASEO VILLA DEL RIO  1,371,125,71221/11/2019

 

I also have a Date table, which has relationship with my sales table and my forecast table, as well as a proyect table, which has relation with both tables.

 

What I would like to do is sum up my sales up to the last date with the forecast sales from the last sales date to the end of the proyect = sales + remaining forecast

 

I am using this formulas:

FUTURE FORECAST =
var lastdate=MAXX(ALL(sales[date]),sales[date])
return
IF(SELECTEDVALUE(Calendare[Date]) > lastdate, SUMX(FORECAST,FORECAST[SALES), BLANK())
and the formula that sums both is:
TOTAL REAL INCOME FORECAST = [SALES] + [FUTURE FORECAST]
 
The problem is that it sums up my Sales but it doesn´t add up my future forecast.
Any ideas. 
Nathaniel_C
Super User
Super User

Hi @agustinmisaza12 ,

IF(SELECTEDVALUE(Calendar[Date]) > Lastdate, SUMX(FORECAST,FORECAST[income]), BLANK())

 So as look at your table, the dates are filtering for each value.  When you get to total, there is no date.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.