Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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.
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 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
@Greg_Deckler Not returning any results. Did I accidentally miss something?
@Greg_Deckler Sorry, I missed updating the value portion, but now there's error in the syntax.
@anna-lee For your first squiggly red line right where it starts, put a ) right before the ,
@Greg_Deckler syntax fixed thank you but still no values pulling through
@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.
@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 |
@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
@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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
20 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
26 | |
10 | |
10 | |
9 | |
6 |