Forum Discussion

Jkaelin's avatar
Jkaelin
Icon for Resolver I rankResolver I
9 years ago

Calculate product by year

Good morning,   Overall, I am seeking to produce a measure that can calculate the product by year.  All my current attempts have failed.  An overview is this:  I have a column of data in which I ca...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Jkaelin

 

Based on your sample data, can you please provide what the expected outcome should look like?

 

Feel free to mock up in Excel or similar to help clarify what you are after. :)

Jkaelin's avatar
Jkaelin
Icon for Resolver I rankResolver I
9 years ago

Phil_Seamark

 

Hey Phil!  Below is a sample data & expected outcome.  The expected outcome is highlighted in yellow.  

 

  • The yellow formula is (like in Excel) =Product(IF(Year(Row Labels=2006),Column B)).  
  • It's the 2006 data, multiplied together.  
  • Also, 1.0165 = (1.02 X 1.00 X 0.98 X 1.01)
  • Quick Note:  Column B is not a column of regular data or a calculated column, it's the pivot table result of measures.  

 

 

 

I was hoping a calculated(productx(...)) would work, but to no avail.  I've tried summarize & other alternatives.  I'm just not that good in DAX yet.  Any help would be much appreciated.

 

Thank you & kindly,

James