for each month show the last day in month

hi everyone,

i want create calculated measure shows cube's measure , on y axis last day of each month

i tried closingperiod function no success

i appreciate assistance!

eventually result should this:

[time].[time h hirarchy].[date]              [mymeasure]

10/20/2013                                                150

09/30/2013                                                120

08/31/2013                                                95

etc...

here's 1 example of tried:

with member [measures].[mynewmeasure] as 
 (
 closingperiod
 (
[time].[time h hirarchy].[month] 
[time].[time h hirarchy].currentmember
 ),
[measures].[mymeasure]
 ),format_string = "currency"

select 
 {
[measures].[mymeasure]
 } on columns
 ,
[time].[time h hirarchy].[date] 
  on rows
[daily]

only recieved in result on y axis days

10.20.13     150

10.19.13     250

10.18.13    40

etc'

hi gilboa ,

try next code last non empty child per month .

with member lastact ([time].[time h hirarchy].currentmember.lastchild,mymeasure),format_string = "currency"
select nonempty([time].[month].[month],lastact) on 1
  , lastact on 0
[daily]

hope helps :)

---

regards, david .

SQL Server  >  SQL Server Analysis Services