MDX Last Month to date not showing value on Day level when month has different number of days

Question

I've used a Last Month to date measure in my SSAS cube. I found it somewhere in the Internet. And it has been set as the following:

iif (
    [Date].[Calendar].currentmember.properties("level_number",Typed)<=2
    ,null
    ,
    iif (
        isempty (ParallelPeriod([Date].[Calendar].[Month],1,[Date].[Calendar].currentmember))
        ,Aggregate(
        {
            StrToMember(Ancestor([Date].[Calendar].currentmember,[Date].[Calendar].[Month]).Lag(1).FirstChild.uniquename)
            :StrToMember(Ancestor([Date].[Calendar].currentmember,[Date].[Calendar].[Month]).Lag(1).LastChild.uniquename)
        },[Measures].[Total actuals Prorata]
        )
           ,SUM(
            MTD(
                ParallelPeriod([Date].[Calendar].[Month]
                                ,1
                                ,[Date].[Calendar].currentmember
                                )
                ),[Measures].[Sales]
            )
        )

It is working as expected on Month level. Even when I drill down on day level it's working! But my problem is the fact that on July 31st for example, I have #VALUE for my Last Month to Date because there is no 31st of June. And it's the same issue for all the other months with less days from the current. The above function seemed to get rid of this problem in the "iif statements" but it doesn't! My Calendar hierarchy is organised by year, quarter, month, day. Any help please?

Answer 1

Does changing the part

Aggregate(
        {
            StrToMember(Ancestor([Date].[Calendar].currentmember,[Date].[Calendar].[Month]).Lag(1).FirstChild.uniquename)
            :StrToMember(Ancestor([Date].[Calendar].currentmember,[Date].[Calendar].[Month]).Lag(1).LastChild.uniquename)
        },[Measures].[Total Actuals Prorata]

to the below work?

    Aggregate(
    {
        Ancestor
              (
               [Date].[Calendar].currentmember,
               [Date].[Calendar].[Month]).Lag(1).FirstChild
              )
        :
       Ancestor
             (
              [Date].[Calendar].currentmember,
              [Date].[Calendar].[Month]).Lag(1).LastChild
             )
    },[Measures].[Sales]

Answer 2

Maybe try to simplify the Aggregate section to a tuple:

IIF(
    [Date].[Calendar].currentmember.properties("level_number",Typed)<=2
   , NULL
   ,IIF(
        ISEMPTY(
            PARALLELPERIOD(
               [Date].[Calendar].[Month]
              ,1
              ,[Date].[Calendar].currentmember
              )
            )
        , (
           Ancestor(
               [Date].[Calendar].currentmember
              ,[Date].[Calendar].[Month]).PREVMEMBER
           ,[Measures].[Total Actuals Prorata]
          )
         ,SUM(
            MTD(
               PARALLELPERIOD(
                 [Date].[Calendar].[Month]
                ,1
                ,[Date].[Calendar].currentmember
               )
              )
            ,[Measures].[Sales]
            )
        )

Answer 3

Thank you guys! I finally made my function work. Indeed, the problem was on the Aggregate statement. In fact this function worked perfectly on a test cube, but I didn't understand why it didn't on another one. So what I did,I just changed the

 Aggregate(
    {
        StrToMember(Ancestor([Date].[Calendar].currentmember,[Date].[Calendar].[Month]).Lag(1).FirstChild.uniquename)
        :StrToMember(Ancestor([Date].[Calendar].currentmember,[Date].[Calendar].[Month]).Lag(1).LastChild.uniquename)
    },[Measures].[Sales]
    )

By

SUM(
    {
        StrToMember(Ancestor([Date].[Calendar].currentmember,[Date].[Calendar].[Month]).Lag(1).FirstChild.uniquename)
        :StrToMember(Ancestor([Date].[Calendar].currentmember,[Date].[Calendar].[Month]).Lag(1).LastChild.uniquename)
    },[Measures].[Sales]
    ) 

And it worked! But I'll also try your functions to check the results. Thank you!