Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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??
Solved! Go to 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.
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.
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:
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |