Help creating dax measure

[anna-lee]

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

[v-yadongf-msft · Accepted solution]

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.

[anna-lee]

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

[Greg_Deckler]

@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

[anna-lee]

@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

[anna-lee]

@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

[Greg_Deckler]

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

[anna-lee]

@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

[Greg_Deckler]

@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.

[anna-lee]

@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

[Greg_Deckler]

@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

[anna-lee]

@Greg_Deckler 

[anna-lee]

@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.