Converting MDX set query result to measure

Hi all,

I have a SET calculated member that returns me the first invoice date of a specific client ID.

The problem is that I want to find a workaround so that I'm not forced to use this set on my rows. I would like to make this a measure so I can use it in my columns and for other purposes.

Could anyone help me with that? Is it possible?

SET [First Invoice Date] AS
		Iif(
			COUNT(
				NonEmpty(
					[Date invoice].[Invoice date].[Invoice date],
					[CurrentSalesPeriod]
					* Ytd(StrToMember(@PAR_Date).Parent.Lag(1).LastChild)
					* [Point of sale].[Client id].CurrentMember
					* {[Measures].[YTD Sales]}
				)
			) = 0,
			{[Date invoice].[Bonus Calendar - Week].[All].UNKNOWNMEMBER.UNKNOWNMEMBER},
			Head(
				NonEmpty(
					[Date invoice].[Invoice date].[Invoice date],
					[CurrentSalesPeriod]
					* Ytd(StrToMember(@PAR_Date).Parent.Lag(1).LastChild)
					* [Point of sale].[Client id].CurrentMember
					* {[Measures].[YTD Sales]}
				), 1
			)
	)

Take in mind, that the currentsalesperiod is a single value, returned by a set

  SET [CurrentSalesPeriod] AS
		Tail( 
			NonEmpty(
				[Point of sale].[Management period].[Management period],
				{[Measures].[Sales amount]}
				* Ytd(StrToMember(@PAR_Date))
				* [Date invoice].[Bonus calendar - Quarter].CurrentMember
				* [Point of sale].[POS id].CurrentMember
			), 1
		)


July 14th, 2015 1:21am

I found a solution somehow.

MEMBER [Measures].[Test] AS


				NonEmpty(
					[Date invoice].[Invoice date].[Invoice date],
					[CurrentSalesPeriod]
					* Ytd([Date invoice].[Bonus calendar - Week].[Bonus week of year].&[2015]&[23].Parent.Lag(1).LastChild)
					* [Point of sale].[Client id].&[46]
					* {[Measures].[YTD Sales]}
				)
			.Item(0).Member_key

The only problem, the result is 1/13/2014. How do I convert this to 2014-01-13?

Free Windows Admin Tool Kit Click here and download it now
July 14th, 2015 2:48am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics