Using ProductX with a measure rather than a column

Anonymous — Wed, 17 Mar 2021 04:56:48 GMT

Hi all

I've got some sales data where I want to project forward total sales amount based on known year on year % change. The data for the Year on Year % change value is a measure that is calculated based of the results of another table.

The data looks like this:

Year	CountryName	Known Sales	YoYChange
2000	Germany	$7,364,111
2001	Germany	$9,398,581	8.30%
2002	Germany	$12,143,723	10.00%
2003	Germany	$15,186,217	12.30%
2004	Germany	$18,090,814	14.00%
2005	Germany	$20,601,219	16.00%
2006	Germany	$23,360,535	15.80%
2007	Germany	$25,346,279	13.10%
2008	Germany	$26,691,696	6.40%
2009	Germany	$29,295,841	2.50%
2010	Germany	$31,046,735	-0.30%
2011	Germany	$31,308,378	-2.80%
2012	Germany	$32,585,961	-3.90%
2013	Germany	$34,091,290	-3.50%
2014	Germany	$41,924,198	-2.80%
2015	Germany	$43,367,394	-1.70%
2016	Germany	$43,886,104	-0.10%
2017	Germany	$44,127,509	0.70%
2018	Germany	$44,935,169	1.40%
2019	Germany	$46,993,614	0.90%
2020	Germany		-0.60%
2021	Germany		2.10%
2022	Germany		1.30%
2023	Germany		1.50%
2024	Germany		1.80%
2025	Germany		2.20%
2026	Germany		2.40%
2027	Germany		2.50%
2028	Germany		2.40%
2029	Germany		2.30%
2030	Germany		2.30%

The data I want outputted is an extra column which basically takes the last known sales amount and multiples out the future year on year growth rates. Expected output would be something likefuture sales in the table below.

Year	CountryName	Known Sales	YoYChange	Future Sales
2000	Germany	$7,364,111
2001	Germany	$9,398,581	8.30%
2002	Germany	$12,143,723	10.00%
2003	Germany	$15,186,217	12.30%
2004	Germany	$18,090,814	14.00%
2005	Germany	$20,601,219	16.00%
2006	Germany	$23,360,535	15.80%
2007	Germany	$25,346,279	13.10%
2008	Germany	$26,691,696	6.40%
2009	Germany	$29,295,841	2.50%
2010	Germany	$31,046,735	-0.30%
2011	Germany	$31,308,378	-2.80%
2012	Germany	$32,585,961	-3.90%
2013	Germany	$34,091,290	-3.50%
2014	Germany	$41,924,198	-2.80%
2015	Germany	$43,367,394	-1.70%
2016	Germany	$43,886,104	-0.10%
2017	Germany	$44,127,509	0.70%
2018	Germany	$44,935,169	1.40%
2019	Germany	$46,993,614	0.90%
2020	Germany		-0.60%	$ 46,711,652
2021	Germany		2.10%	$ 47,692,597
2022	Germany		1.30%	$ 48,312,601
2023	Germany		1.50%	$ 49,037,290
2024	Germany		1.80%	$ 49,919,961
2025	Germany		2.20%	$ 51,018,200
2026	Germany		2.40%	$ 52,242,637
2027	Germany		2.50%	$ 53,548,703
2028	Germany		2.40%	$ 54,833,872
2029	Germany		2.30%	$ 56,095,051
2030	Germany		2.30%	$ 57,385,237

I'm nearly certain the approach used in this video by Curbal is the way to do it - that is using PRODUCTX iterator function to calculate out. However, ProductX only takes acolumn as a its second argument...but I want to pass a measure.

The PRODUCTX function takes as its first argument a table, or an expression that returns a table. The second argument is a column that contains the numbers for which you want to compute the product, or an expression that evaluates to a column.

I cannot find a way to do the same thing using a measure - after days of research. Does anyone know of a workaround or how I could potentially parse my measure as a column so the ProductX function can evaulate future sales?

Any help would be highly appreciated!

Cheers

Re: Using ProductX with a measure rather than a column

amitchandak — Wed, 17 Mar 2021 06:58:14 GMT

Anonymous , check if measure like this can help

measure =
var _year = maxx(allselected(Table),Table[Year])
return
maxx(filter(Table, Table[year] = _year),[YoYChange])* PRODUCTX(filter(allselected(Table), Table[Year] <=max(Table]) & &Table[year] > _year ), 1+ YoYChange)

Re: Using ProductX with a measure rather than a column

Anonymous — Wed, 17 Mar 2021 07:08:53 GMT

Thanks for the reply amitchandak. I'll have to give that a whirl in the morning...was wondering what that measure is actually doing though?

topic Using ProductX with a measure rather than a column in DAX Commands and Tips

Using ProductX with a measure rather than a column

Re: Using ProductX with a measure rather than a column

Re: Using ProductX with a measure rather than a column