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

    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.