Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
suresh123
New Member

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 YearStock 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

 

 

 

 

 

1 ACCEPTED SOLUTION
itsmebvk
Continued Contributor
Continued Contributor

@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

 

Example.PNG

 

Please correct me if I am missing something.

 

Regards

Vamsi

 

View solution in original post

5 REPLIES 5
v-frfei-msft
Community Support
Community Support

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

Capture.PNG

 

For more details, please check the pbix as attached.

 

Regards,

Frank

 

 

 

 

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Dear Frank,

 

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

 

 

itsmebvk
Continued Contributor
Continued Contributor

@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

 

Example.PNG

 

Please correct me if I am missing something.

 

Regards

Vamsi

 

Ashish_Mathur
Super User
Super User

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?

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AlexisOlson
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.