DAX FORMULA DOESN'T SUMS

[agustinmisaza12]

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.

ANY SOLUTION??

[v-frfei-msft · Accepted solution]

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.

[agustinmisaza12]

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

FORECAST TABLE:

CC	PROJECT	FORECAST	DATE
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	-	1/10/2018
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	3,546,447,507	1/11/2018
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,277,560	1/12/2018
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,277,560	1/01/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,277,560	1/02/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,277,560	1/03/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,277,560	1/04/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	759,459,048	1/05/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,277,560	1/06/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,277,560	1/07/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,277,560	1/08/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	2,123,402,473	1/09/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,277,560	1/10/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	1,371,125,712	1/11/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	1,083,333,177	1/12/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	1,083,333,177	1/01/2020
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	1,083,333,177	1/02/2020
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	1,083,333,177	1/03/2020

SALES TABLE:

CC	PROJECT	SALES	Date
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	-	1/11/2018
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	3,546,447,507	8/11/2018
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,760	3/12/2018
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,759	8/01/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,759	6/02/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,759	8/03/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	-	3/04/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,759	5/04/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	7,182,289	3/05/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,759	6/05/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,759	5/06/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,759	5/07/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,759	15/08/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,759	12/09/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	1,371,125,714	20/09/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	-      16,473,435	16/10/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	752,276,755	21/10/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	-           636,073	5/11/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	-        1,497,408	19/11/2019
OE699	CENTRO COMERCIAL PASEO VILLA DEL RIO	1,371,125,712	21/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]

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