Forum Discussion

suresh123's avatar
suresh123
New Member
7 years ago
Solved

DAX Formula gives error

Dear All, My data table given below. FIN Year Last year and Previous Year are in ine column and trying to get the GR% and below Dax formula made but while adding the measure to Report im getting the...
Need Help
  • itsmebvk's avatar
    itsmebvk
    7 years ago

    suresh123

     

    I tried it in differnt way and got the output as per your expectections. You can also try something like this (I know there might be different ways to achieve).

     

     Create a column

     

    Year_Calc = VALUE(LEFT(Sheet1[FIN Year],4))

     

    Create a measure

     

    Growth% = var YearGrowth= CALCULATE(SUM(Sheet1[Stock Value]),FILTER(ALL(Sheet1),Sheet1[Year_Calc]=MIN(Sheet1[Year_Calc])-1)) return DIVIDE(SUM(Sheet1[Stock Value])-YearGrowth,YearGrowth)*100

     

    I created a dummy column "Year Type" for testing in Matrix

     

     

    Please correct me if I am missing something.

     

    Regards

    Vamsi

     

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi suresh123,

 

Please update your formula as below.

 

GR% = DIVIDE(CALCULATE(SUM(Sheet1[Stock Value]),FILTER(Sheet1,Sheet1[FIN Year]="2018-19")),CALCULATE(SUM(Sheet1[Stock Value]),FILTER(Sheet1,Sheet1[FIN Year]="2017-18")))-1

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

 

 

 

 

 

suresh123's avatar
suresh123
New Member
7 years ago

Dear Frank,

 

Yes formula working but when im tring to bring to Matrix outputs are coming same 100%.

 

 

  • itsmebvk's avatar
    itsmebvk
    Continued Contributor
    7 years ago

    suresh123

     

    I tried it in differnt way and got the output as per your expectections. You can also try something like this (I know there might be different ways to achieve).

     

     Create a column

     

    Year_Calc = VALUE(LEFT(Sheet1[FIN Year],4))

     

    Create a measure

     

    Growth% = var YearGrowth= CALCULATE(SUM(Sheet1[Stock Value]),FILTER(ALL(Sheet1),Sheet1[Year_Calc]=MIN(Sheet1[Year_Calc])-1)) return DIVIDE(SUM(Sheet1[Stock Value])-YearGrowth,YearGrowth)*100

     

    I created a dummy column "Year Type" for testing in Matrix

     

     

    Please correct me if I am missing something.

     

    Regards

    Vamsi