topic Re: Help creating dax measure in DAX Commands and Tips

Help creating dax measure

anna-lee — Wed, 16 Nov 2022 22:10:58 GMT

Hi,
I am trying to create a measure/column that will calculate:
revenue / ((accountsreceivable - year before accountsreceivable) / 2)
else 0
and still be relative to the vendor code & year.

Vendor Code	Description	Concat	Year	Value
MA33C4	accountsreceivable	MA33C4accountsreceivable	2021	1774
MA33C4	accountsreceivable	MA33C4accountsreceivable	2020	1501
MA33C4	accountsreceivable	MA33C4accountsreceivable	2019	1103
MA33C4	accountsreceivable	MA33C4accountsreceivable	2018	4615
MA33C4	accountsreceivable	MA33C4accountsreceivable	2017	6852
MA33C4	accountsreceivable	MA33C4accountsreceivable	2016	1827
MA33C4	accountspayable	MA33C4accountspayable	2021	1094
MA33C4	accountspayable	MA33C4accountspayable	2020	1697
MA33C4	accountspayable	MA33C4accountspayable	2019	2144
MA33C4	accountspayable	MA33C4accountspayable	2018	3498
MA33C4	accountspayable	MA33C4accountspayable	2017	3624
MA33C4	accountspayable	MA33C4accountspayable	2016	1109
MA33C4	revenue	MA33C4revenue	2021	1480
MA33C4	revenue	MA33C4revenue	2020	1502
MA33C4	revenue	MA33C4revenue	2019	1002
MA33C4	revenue	MA33C4revenue	2018	7624
MA33C4	revenue	MA33C4revenue	2017	5489
MA33C4	revenue	MA33C4revenue	2016	4850
MA33C4	costofgoods	MA33C4costofgoods	2021	9846
MA33C4	costofgoods	MA33C4costofgoods	2020	9204
MA33C4	costofgoods	MA33C4costofgoods	2019	6235
MA33C4	costofgoods	MA33C4costofgoods	2018	4477
MA33C4	costofgoods	MA33C4costofgoods	2017	4052
MA33C4	costofgoods	MA33C4costofgoods	2016	2218
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2021	0
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2020	1009
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2019	1903
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2018	1566
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2017	1083
RQ020G	accountsreceivable	RQ020Gaccountsreceivable	2016	0
RQ020G	accountspayable	RQ020Gaccountspayable	2021	0
RQ020G	accountspayable	RQ020Gaccountspayable	2020	4307
RQ020G	accountspayable	RQ020Gaccountspayable	2019	2743
RQ020G	accountspayable	RQ020Gaccountspayable	2018	2272
RQ020G	accountspayable	RQ020Gaccountspayable	2017	2397
RQ020G	accountspayable	RQ020Gaccountspayable	2016	0
RQ020G	revenue	RQ020Grevenue	2021	0
RQ020G	revenue	RQ020Grevenue	2020	3582
RQ020G	revenue	RQ020Grevenue	2019	4239
RQ020G	revenue	RQ020Grevenue	2018	4000
RQ020G	revenue	RQ020Grevenue	2017	5325
RQ020G	revenue	RQ020Grevenue	2016	0
RQ020G	costofgoods	RQ020Gcostofgoods	2021	0
RQ020G	costofgoods	RQ020Gcostofgoods	2020	2904
RQ020G	costofgoods	RQ020Gcostofgoods	2019	3712
RQ020G	costofgoods	RQ020Gcostofgoods	2018	3708
RQ020G	costofgoods	RQ020Gcostofgoods	2017	5086
RQ020G	costofgoods	RQ020Gcostofgoods	2016	0

Re: Help creating dax measure

Greg_Deckler — Wed, 16 Nov 2022 23:35:20 GMT

anna-lee Maybe:

Measure = VAR __Year = MAX('Table'[Year]) VAR __Revenue = SUMX(FILTER('Table',[Description] = "revenue"),[Value]) VAR __AR = SUMX(FILTER('Table',[Description] = "accountsreceivable"),[Value]) VAR __ARPY = SUMX(FILTER('Table',[Description] = "accountsreceivable" && [Year] = __Year - 1),[Value]) VAR __Result = DIVIDE(__Revenue, (__AR - __ARPY) / 2) RETURN __Result

Re: Help creating dax measure

anna-lee — Thu, 17 Nov 2022 14:37:26 GMT

Greg_Deckler Not returning any results. Did I accidentally miss something?

Measure =

VAR __Year = MAX('Table'[Year])

VAR __Revenue = SUMX(FILTER('Table','Table'[Description] = "revenue"),[Value])

VAR __AR = SUMX(FILTER('Table','Table'[Description] = "accountsreceivable"),[Value])

VAR __ARPY = SUMX(FILTER('Table','Table'[Description] = "accountsreceivable" && 'Table'[Year] = __Year - 1),[Value])

VAR __Result = DIVIDE(__Revenue, (__AR - __ARPY) / 2)

RETURN

__Result

Re: Help creating dax measure

anna-lee — Thu, 17 Nov 2022 15:02:50 GMT

Greg_Deckler Sorry, I missed updating the value portion, but now there's error in the syntax.

Measure =

VAR __Year = MAX('Table'[Year])

VAR __Revenue = SUMX(FILTER('Table','Table'[Description] = "revenue"),'Table'[Value])

VAR __AR = SUMX(FILTER('Table','Table'[Description] = "accountsreceivable"),'Table'[Value])

VAR __ARPY = SUMX(FILTER('Table','Table'[Description] = "accountsreceivable" && 'Table'[Year]= __Year - 1,'Table'[Value])

VAR __Result = DIVIDE(__Revenue, (__AR - __ARPY) / 2)

RETURN

__Result

Re: Help creating dax measure

Greg_Deckler — Thu, 17 Nov 2022 16:45:34 GMT

anna-lee For your first squiggly red line right where it starts, put a ) right before the ,

Re: Help creating dax measure

anna-lee — Thu, 17 Nov 2022 17:33:13 GMT

Greg_Deckler syntax fixed thank you but still no values pulling through

Measure =

VAR __Year = MAX('Table'[Year])

VAR __Revenue = SUMX(FILTER('Table','Table'[Description] = "revenue"),'Table'[Value])

VAR __AR = SUMX(FILTER('Table','Table'[Description] = "accountsreceivable"),'Table'[Value])

VAR __ARPY = SUMX(FILTER('Table','Table'[Description] = "accountsreceivable" && 'Table'[Year]= __Year - 1),'Table'[Value])

VAR __Result = DIVIDE(__Revenue, (__AR - __ARPY) / 2)

RETURN

__Result

Re: Help creating dax measure

Greg_Deckler — Thu, 17 Nov 2022 17:36:12 GMT

anna-lee I've attached the PBIX I am using where it is working. It is Table5 and Measure. There is a matrix visual on the first page. Attached below signature.

Re: Help creating dax measure

anna-lee — Thu, 17 Nov 2022 19:45:18 GMT

Greg_Deckler thank you for sharing pbix file! However, the values I've calculated in excel are not in line with what is populating in the matrix.

	2016	2017	2018	2019	2020	2021
MA33C4	5.31	2.18	-6.82	-0.57	7.55	10.84
RQ020G		9.83	16.56	25.16	-8.01	0.00

Re: Help creating dax measure

Greg_Deckler — Fri, 18 Nov 2022 02:36:31 GMT

anna-lee Can you share the Excel? Also, maybe:

Measure = VAR __Year = MAX('Table5'[Year]) VAR __Revenue = SUMX(FILTER('Table5',[Description] = "revenue"),[Value]) VAR __AR = SUMX(FILTER('Table5',[Description] = "accountsreceivable"),[Value]) VAR __ARPY = SUMX(FILTER(ALL('Table5'),[Description] = "accountsreceivable" && [Year] = __Year - 1),[Value]) VAR __Result = DIVIDE(__Revenue, (__AR - __ARPY) / 2) RETURN __Result

Re: Help creating dax measure

anna-lee — Wed, 23 Nov 2022 14:34:39 GMT

Greg_Deckler

Re: Help creating dax measure

anna-lee — Mon, 21 Nov 2022 21:30:21 GMT

Greg_Deckler
I think the issue lies with the VAR __ARPY line as when I separate and run them individually this one doesn't populate a value.
Is it also possible to throw a conditional statement that if the sum of all of the previous year = $0, then to give $0 else calculate the measure? The measure shouldn't populate if the prior year's data was not entered.

Re: Help creating dax measure

v-yadongf-msft — Wed, 23 Nov 2022 08:55:02 GMT

Hi anna-lee

Please create a measure:

Measure = VAR __Year = MAX('Table'[Year]) VAR __Revenue = SUMX(FILTER('Table',[Description] = "revenue" && 'Table'[Vendor Code] = SELECTEDVALUE('Table'[Vendor Code])),[Value]) VAR __AR = SUMX(FILTER('Table',[Description] = "accountsreceivable" && 'Table'[Vendor Code] = SELECTEDVALUE('Table'[Vendor Code])),[Value]) VAR __ARPY = SUMX(FILTER(ALL('Table'),[Description] = "accountsreceivable" && [Year] = __Year - 1 && 'Table'[Vendor Code] = SELECTEDVALUE('Table'[Vendor Code])),[Value]) VAR __Result = DIVIDE(__Revenue, (__AR - __ARPY) / 2) RETURN __Result

The result in matrix is the same as the result in excel:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Help creating dax measure

anna-lee — Wed, 23 Nov 2022 14:34:13 GMT

v-yadongf-msft
this worked perfectly. thank you!