Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
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)