Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

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]
;

Open in new window

0
ISALRW
Asked:
ISALRW
2 Solutions
 
itcoupleCommented:
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
 
HwkrangerCommented:
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 <=
	HEAD(EXISTING [Date].[Date].[Date]).ITEM(0).ITEM(0).LAG(365).MEMBERVALUE
    ),
    Aggregate(Generate(EXISTING [Date].[Calendar].[Month].Members, {ParallelPeriod([Date].[Calendar].[Year], 1)}), [Measures].[Closed Relationship])
), 
FORMAT_STRING = "#,#;(#,#);0;\-", 
VISIBLE = 1;

Open in new window

CREATE MEMBER CURRENTCUBE.[MEASURES].[Closed Relationship - STLY (Complete)]
 AS SUM(
	FILTER
	(EXISTING([Geography].[Agency].[Agency]),
	[Geography].[Import Start Date].CURRENTMEMBER.MEMBERVALUE <=
	HEAD(EXISTING [Date].[Date].[Date]).ITEM(0).ITEM(0).LAG(365).MEMBERVALUE
    ),
    Aggregate(Generate(EXISTING [Date].[Calendar].[Month].Members, {ParallelPeriod([Date].[Calendar].[Year], 1)}), [Measures].[Closed Relationship])
), 
FORMAT_STRING = "#,#;(#,#);0;\-", 
VISIBLE = 1;

Open in new window

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now