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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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

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

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

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

      • 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