Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |