Forum Discussion

anna-lee's avatar
anna-lee
Icon for Helper I rankHelper I
3 years ago
Solved

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.  ...
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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's avatar
anna-lee
Icon for Helper I rankHelper I
3 years ago

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's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
    anna-lee
    Icon for Helper I rankHelper I
    3 years ago

    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
    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      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