# Find prevoius date with data

Folks,

I am trying to create what seems to be a pretty simple calculated member, which will go back (from my current date) and find the previous date with any data entered (some of the dates have no info entered).  I am using recursion as follows:

``````with
member [Date].[Calendar].[BeforeCurrentWithData] as
iif(
([Date].[Calendar].currentmember.prevmember, [Measures].[Sales]) <> 0,
[Date].[Calendar].currentmember.prevmember,
[Date].[Calendar].[BeforeCurrentWithData]
)
select
{[Date].[Calendar].[BeforeCurrentWithData]} on columns,
[Date].[Calendar2].[Date].members on rows
from [MyCube]
;
``````
###### Who is Participating?

Commented:
You can use LAG or PARRALLEL PERIOD  take your pick.

http://msdn.microsoft.com/en-us/library/ms144866.aspx
http://msdn.microsoft.com/en-us/library/ms144866.aspx

See below for a example, I use both Lag and ParallelPeriod:
``````CREATE MEMBER CURRENTCUBE.[MEASURES].[Closed Relationship - STLY (Complete)]
AS SUM(
FILTER
(EXISTING([Geography].[Agency].[Agency]),
[Geography].[Import Start Date].CURRENTMEMBER.MEMBERVALUE <=
),
Aggregate(Generate(EXISTING [Date].[Calendar].[Month].Members, {ParallelPeriod([Date].[Calendar].[Year], 1)}), [Measures].[Closed Relationship])
),
FORMAT_STRING = "#,#;(#,#);0;\-",
VISIBLE = 1;
``````
``````CREATE MEMBER CURRENTCUBE.[MEASURES].[Closed Relationship - STLY (Complete)]
AS SUM(
FILTER
(EXISTING([Geography].[Agency].[Agency]),
[Geography].[Import Start Date].CURRENTMEMBER.MEMBERVALUE <=
),
Aggregate(Generate(EXISTING [Date].[Calendar].[Month].Members, {ParallelPeriod([Date].[Calendar].[Year], 1)}), [Measures].[Closed Relationship])
),
FORMAT_STRING = "#,#;(#,#);0;\-",
VISIBLE = 1;
``````
0

Commented:
Hi

The way I would do that. I would identify current date (I use VBA MDX functions) and filter for non empty based on measure group and take the first one using tail function.

There might be a better way of doing that. Maybe some last non empty but I'm sure.

Hope that gives you more options to experiment with
Regards
Emil

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.