Forum Discussion
anna-lee
Helper I
Helper IHelp creating dax measure
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 |
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 __ResultThe 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.
12 Replies
- Greg_Deckler
Community Champion
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-leeGreg_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
v-yadongf-msftCommunity Support
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 __ResultThe 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!
