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

Calculate Sales for the recent year

I have a two fields date and sales...There are multiple years of data...I created  a master dimension called "Year".  What formula do I use to calculate sum(sales) for the most recent year without using If statement?
Since "Year" is a master dimension, I cannot use it in the set expression right to filter the data based on year?

And also can Master dimensions be used in set expressions or can only Fields in the data model be used?

Labels (4)
3 Replies
duchezbr
Contributor III
Contributor III

If I understand the question correctly this may work:

Sum({<Year={'$(=Max(Year))'}>} Sales)

For example, in a KPI this will display the sum of sales for the Max Year.  If you select multiple years it will show you the sum of sales for the Max Year.

Master Items can't be used in set expressions but fields, expressions, and variables can.

 

lost_rabbit
Contributor III
Contributor III
Author

Edited the question . Let me know if it is clear 

Sum({<Year={'$(=Max(Year))'}>} Sales) will work but only when 'Year' is a field in the data model. In my case, 'Year' is a master dimension

Quy_Nguyen
Specialist
Specialist

You can use the date field for your YTD calculation (make sure you're having a correct date format)

Sum({<Date={">=$(=YearStart(Max(Date)))"}>}Sales)