DAX Formula gives error

[suresh123]

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 error with 100% or Infinity.

NOTE: I cant use the sameperiod in my measure since this is stock data and comparing with different dates

GR%=Calculate(sum(Sheet1[Stock Value]),sheet([FIN Year]="2018-19")/(sum(Sheet1[Stock Value]),sheet([FIN Year]="2017-18")-1

FIN Year	Stock Value
2017-18	6,000
2018-19	5,000
2017-18	6,000
2017-18	4,000
2018-19	2,000
2018-19	6,000
2018-19	5,000

[v-frfei-msft]

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]

Dear Frank,

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

[itsmebvk · Accepted solution]

@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

[Ashish_Mathur]

Hi,

Considering you have a FIN YEAR column, one should be able to generate a data from it and then use the Date and Time intelligence functions.  Share the following:

1.  How does one interpret different values for the same FIN YEAR.  There should be other columns there as well; and
2.  What is the Financial Year end?

[AlexisOlson]

I don't understand how you are getting anything from that DAX. It's missing parentheses and appears like 'sheet' is supposed to be a function.