Forum Discussion
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 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 |
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
5 Replies
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.
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:
- How does one interpret different values for the same FIN YEAR. There should be other columns there as well; and
- What is the Financial Year end?
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
Dear Frank,
Yes formula working but when im tring to bring to Matrix outputs are coming same 100%.
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
