Forum Discussion
DAX Time Formula - SSAS 2008
Hello,
I connected to my SSAS 2008 cube and trying to do some date based analysis and having some issue. We have DimDate table for each date and I'm trying to compare this year sales with previous year and here is the formula:
Revenue Sales Prev Year = CALCULATE(SUM('F Cube'[Revenue Sales]),PREVIOUSYEAR('F Sakes'[Date]))
And when I drop this field on the report, I don't see any value. Not sure if I need to do something different when workign with Cube.
Help!
P
This formula worked with my date lookup table
Calculate(SUM(Data_Table_Name[Field_To_Sum]),previousyear('Calendar_Table_Name'[Date_Field]),all('Calendar_Table_Name'))If your year doesn't end on December 31 you can add a parameter to the previous year function
mine was showing blank like yours until I added the to the calculate function
all('Calendar_Table_Name')
2 Replies
PREVIOUSYEAR returns the previous year, derived from the last date in the current context. So probably in your report, the last date in 'F Sakes'[Date] is such that the previous year doesn't contain any data. This is common when your date dimension contains dates until, say 2020 or so.
BTW, if you have a dimDate table, you should use the date column from dimDate instead of using a date column from a fact table.
Also, keep in mind that PREVIOUSYEAR returns a whole year. If you want to compare on e.g. a monthly basis, use SAMEPERIODLASTYEAR instead.
This formula worked with my date lookup table
Calculate(SUM(Data_Table_Name[Field_To_Sum]),previousyear('Calendar_Table_Name'[Date_Field]),all('Calendar_Table_Name'))If your year doesn't end on December 31 you can add a parameter to the previous year function
mine was showing blank like yours until I added the to the calculate function
all('Calendar_Table_Name')
