In cell A1 we have this:
=CUBEMEMBER("OurCube","TAIL([Date].[Date - Calendar Month].[Calendar Day].MEMBERS,1).item(0)","TargetMember")
It works fine and returns a single member that is yesterday.
In A2 we have a formula that is attempting to return the actual date - so I thought the CUBEMEMBERPROPERTY function would work:
=CUBEMEMBERPROPERTY("OurCube",A1,"member_caption")
The above returns #N/A
I don't know what CUBEMEMBERPRPERTY does but apparently it doesn't mean what you think it means! If you need to get a certain property of a field according to another field, this is the way to do it: Let's say, I wanted the Financial Year's month name (FY Month Name) based on a certain date key (I live in Australia, the financial year finishes at June):
=CUBEMEMBER("ThisWorkbookDataModel", "EXISTS([Dim Period].[FY Month Name].Children, [Dim Period].[Datekey].[20160731])")
And if the value of "20160731" has been in a certain cell, it would go like this:
=CUBEMEMBER("ThisWorkbookDataModel", "EXISTS([Dim Period].[FY Month Name].Children, [Dim Period].[Datekey].["&A8&"])")
Both would give me the correct answer: 01 - July
And I would like to thank the following posts for their help: https://wessexbi.wordpress.com/2014/02/16/a-cubememberproperty-equivalent-with-powerpivot/
http://www.mrexcel.com/forum/power-bi/730287-function-cubememberproperty-always-return-n.html