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