MDX with Excel - for future reference

Let's say you have data model that you want to enquire on. If you're using Power Query (in Excel) you might want to investigate a little further. In order to do that you need to unleash the power of MDX, which is complete and utterly crazy. The problem with MDX (unlike plain Excel functions) is that it's really hard to know what you're doing and there's very little documentation. Nevertheless it's important to read the documentation, and you'll get the hand of it - after a while.

So, how do I find out what was the last date in my cube that actually gave me some data?

My model consist of an SQL database, in which there's a table called dimPeriod, and measures which are stored in a Fact Table. The data is queried from Excel using Stored Procedures, and reported via PowerQuery. Obviously enough, the dimPeriod table includes days from the last couple of years to the next 20+ years. That's enough, and it's great as it warns me if I'm trying to enter the wrong date (yes, I'm using foreign keys to enforce the referential integrity).
Let's just say that [ALL ACP] is the table where there are measures. So I thought of using something like TopCount function to create a set. Unfortunately, it didn't give me the correct results:

=CUBESET("ThisWorkbookDataModel","TopCount([ALL ACP].[Datekey].Children,5)","Max Date Reported")


Explanation:
Cubeset - creates a cube set
"ThisWorkbookDataModel" - the data model that is stored in this workbook. That's true for Excel 2013, and basically, once you type the "Cubeset" function, open bracekts and the double qoute, it comes up with the name of the model.
TopCount - give you the first number
[All Acp] - the table that has measures in it
DateKey - yep, that's the datekey
Children - otherwise you'll get "all"
5 - just a number. I could (or should) have used 1, but 5 gives me a range of numbers to work with.

After inserting that set to a cell in Excel, all I needed was to enquire after that set:

=CUBERANKEDMEMBER("ThisWorkbookDataModel",$G$30,1)


G30 is where the code above is inserted.
A problem: I got the minimum date.
Solution:

=CUBESET("ThisWorkbookDataModel","BottomCount([ALL ACP].[Datekey].Children,5)","Max Date Reported")


Changing from TopCount to BottomCount gave me the latest date to come in. Please note that there are no measures in the query.

You have two options to query on the result, which is in DateKey format.
The first one is to query the relevant data member. Let's say, you're looking for FY Year, you would use the following code:

=CUBEMEMBER("ThisWorkbookDataModel","EXISTS([Dim Period].[FY Month Name].children, [Dim Period].[DateKey].["&G32&"])")

Where G32 is where you result is
Alternatively, just get out the year, the month and the date using:
Year

 =LEFT(G32,4)

Month:

=MID(G32,5,2)

Day:

=RIGHT(G32,2)

Combining the Excel Formulas with the MDX ones gives you lots of power to manipulate the end results.