Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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
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
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:
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |