Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
anna-lee
Helper I
Helper I

Help 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 CodeDescriptionConcatYearValue
MA33C4accountsreceivableMA33C4accountsreceivable20211774
MA33C4accountsreceivableMA33C4accountsreceivable20201501
MA33C4accountsreceivableMA33C4accountsreceivable20191103
MA33C4accountsreceivableMA33C4accountsreceivable20184615
MA33C4accountsreceivableMA33C4accountsreceivable20176852
MA33C4accountsreceivableMA33C4accountsreceivable20161827
MA33C4accountspayableMA33C4accountspayable20211094
MA33C4accountspayableMA33C4accountspayable20201697
MA33C4accountspayableMA33C4accountspayable20192144
MA33C4accountspayableMA33C4accountspayable20183498
MA33C4accountspayableMA33C4accountspayable20173624
MA33C4accountspayableMA33C4accountspayable20161109
MA33C4revenueMA33C4revenue20211480
MA33C4revenueMA33C4revenue20201502
MA33C4revenueMA33C4revenue20191002
MA33C4revenueMA33C4revenue20187624
MA33C4revenueMA33C4revenue20175489
MA33C4revenueMA33C4revenue20164850
MA33C4costofgoodsMA33C4costofgoods20219846
MA33C4costofgoodsMA33C4costofgoods20209204
MA33C4costofgoodsMA33C4costofgoods20196235
MA33C4costofgoodsMA33C4costofgoods20184477
MA33C4costofgoodsMA33C4costofgoods20174052
MA33C4costofgoodsMA33C4costofgoods20162218
RQ020GaccountsreceivableRQ020Gaccountsreceivable20210
RQ020GaccountsreceivableRQ020Gaccountsreceivable20201009
RQ020GaccountsreceivableRQ020Gaccountsreceivable20191903
RQ020GaccountsreceivableRQ020Gaccountsreceivable20181566
RQ020GaccountsreceivableRQ020Gaccountsreceivable20171083
RQ020GaccountsreceivableRQ020Gaccountsreceivable20160
RQ020GaccountspayableRQ020Gaccountspayable20210
RQ020GaccountspayableRQ020Gaccountspayable20204307
RQ020GaccountspayableRQ020Gaccountspayable20192743
RQ020GaccountspayableRQ020Gaccountspayable20182272
RQ020GaccountspayableRQ020Gaccountspayable20172397
RQ020GaccountspayableRQ020Gaccountspayable20160
RQ020GrevenueRQ020Grevenue20210
RQ020GrevenueRQ020Grevenue20203582
RQ020GrevenueRQ020Grevenue20194239
RQ020GrevenueRQ020Grevenue20184000
RQ020GrevenueRQ020Grevenue20175325
RQ020GrevenueRQ020Grevenue20160
RQ020GcostofgoodsRQ020Gcostofgoods20210
RQ020GcostofgoodsRQ020Gcostofgoods20202904
RQ020GcostofgoodsRQ020Gcostofgoods20193712
RQ020GcostofgoodsRQ020Gcostofgoods20183708
RQ020GcostofgoodsRQ020Gcostofgoods20175086
RQ020GcostofgoodsRQ020Gcostofgoods20160
1 ACCEPTED SOLUTION
v-yadongf-msft
Community Support
Community 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
__Result

 

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

vyadongfmsft_0-1669193661225.png

vyadongfmsft_1-1669193674060.png

 

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.

View solution in original post

12 REPLIES 12
v-yadongf-msft
Community Support
Community 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
__Result

 

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

vyadongfmsft_0-1669193661225.png

vyadongfmsft_1-1669193674060.png

 

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.

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

Greg_Deckler
Community Champion
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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

annalee_0-1668695888420.png

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

 

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

annalee_1-1668697404784.png

 

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler syntax fixed thank you but still no values pulling through

 

annalee_0-1668706450928.png

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

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

 

 201620172018201920202021
MA33C45.312.18-6.82-0.577.5510.84
RQ020G 9.8316.5625.16-8.010.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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.