how to get the 100 days before date from Todays Date in Dimdatedimension

Question

I have small doubt in ssas cube datasource view level. I want add one new column(last100days) in Dimdate timension and that column show last 100 days information. I Tried in sql server level like below query add new column(last100days) in dimdate dimension 1)update dimdate set last100days='01-01-1900' 2)update dimdate set last100days=[StandardDate] WHERE [StandardDate] >= DATEADD(day,-100, getdate()) and [StandardDate] <= getdate()

That time i will get accurate result in dimdate dimension. same way I tried In datasoure view level in dimdatedimension level right click on that and choose new namedcalculation and I give column name is last100days and enter expression like [StandardDate] >= DATEADD(day,-100, getdate()) and [StandardDate] <= getdate() that time it show error like below

TITLE: Microsoft Visual Studio

Deferred prepare could not be completed. Statement(s) could not be prepared. Incorrect syntax near the keyword 'AS'. Incorrect syntax near the keyword 'update'.

---

BUTTONS:

OK..I

-

---

enter code here

---

add new column and that column have from today to last100 days dates .please help me how to resolve this issuse in ssas cube side

Answer 1

You need to enter an expression that returns a value. All yours does is test conditions.

Try this instead:

CASE 
  WHEN  [StandardDate] >= DATEADD(day,-100, getdate()) AND [StandardDate] <= getdate()
       THEN [StandardDate]
  ELSE NULL
END

Answer 2

You don't really need to create a measure for this. You need a new column in your date dimension to be indicative of whether the date is older than 100 days or not. Let's say that column is called RollingLast100days. You need to make a minor adjustment to @tab-alleman's code.

CASE 
  WHEN  DATEDIFF(dd, [StandardDate], GETDATE()) <=100
       THEN 1
  ELSE 0
END

Now that the column is ready, if you want to see only last 100 days worth of data, you just need to have a small additional condition.

WHERE [Dim Date].[Date].RollingLast100days = 1

e.g. Fetching the sales for the last 100 days only:

SELECT [Measures].[Sales Amount] ON 0
FROM    [YourCube]
WHERE [Dim Date].[Date].RollingLast100days = 1

Fetching a list of products sold in the last 100 days:

SELECT [Measures].[Sales Amount] ON 0,
Product.Products.MEMBERS ON 1
FROM    [YourCube]
WHERE [Dim Date].[Date].RollingLast100days = 1

Hope this helps.