I want to filter my query in a data set in a way, where I got dates from beginning of the month until yesterday. First part is easy, I'm passing month from report parameters, so I got values from every day in month, but somehow I have to limit this until yesterday. I tried putting this expression in where clause, but it didn't work at all since I don't have date on rows: FILTER([Date of shipment].[Date], [Date of shipment].[Date] < Format(Now(), "yyyyMMdd")
.
I know I could filter rows, but important thing is, I don't want Date to be displayed on the rows.
Edit: additionally I can use parameter supplied by main report, which is yesterday's date. But how do I limit date without putting it on rows? Something like this doesn't works: IIF( STRTOSET(@ShipmentDate, CONSTRAINED).Count = 1, STRTOSET(@ShipmentDate, CONSTRAINED), [Shipment Date].[Date] < @ShipmentDate))
You already have something similar to this:
SELECT
{} ON 0
,
[Date].[Calendar].[Date].&[20050101]
:
StrToMember
('[Date].[Calendar].[Date].&[20050105]' //<<hard-coded to illustrate
,constrained
) ON 1
FROM [Adventure Works];
Returns:
Most cubes have a multi-level date hierarchy - so you could change your code to something like the so that next year you do not need to change the hard-coded bit:
SELECT
{} ON 0
,
Descendants
(
Exists
(
[Date].[Calendar].[Calendar Year].MEMBERS
,StrToMember
(@ShipmentDate
,constrained
)
).Item(0)
,[Date].[Calendar].[Date]
).Item(0)
:
StrToMember
(@ShipmentDate
,constrained
) ON 1
FROM [Adventure Works];
If @ShipmentDate
is set to '[Date].[Calendar].[Date].&[20060105]'
then I get the following: