Using array functions with a data model

[surma3]

I am trying to simulate a Pivot Table using the new array functions in Excel 365. I have loaded my data from a secondary workbook into the data model (WtData). In it is a date field (Date). I want to list the years in the Date field across columns. I am guessing that I need something like "TRANSPOSE(UNIQUE(YEAR(?)))". But I cannot find a solution for how to specify all the dates in the table. Can it be done? If so, how? Thanks.

 

[jasonb75]

TRANSPOSE(UNIQUE(YEAR(?)))

With a range of dates in place of the question mark, this works fine for me. If you're getting errors then it could mean that your dates are not valid.

 

[surma3]

The dates are fine. I do not see a way to designate the range since it is in the data model. I am thinking that I need to use CUBE functions to reference the data model.

 

[jasonb75]

The only thing that I can see which might have even a remote chance of doing what you want would be something like

TRANSPOSE(UNIQUE(YEAR(CUBESET("WtData","[Date]",0))))

Although I'm not entirely convinced that it will work.

 

[surma3]

I have had success. I am not sure this is the best way, but this does work. I post my solution so that it can possibly help others.

=TRANSPOSE(UNIQUE(YEAR(CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","[WtData].[Date].[All].children"),SEQUENCE(CUBESETCOUNT(CUBESET("ThisWorkbookDataModel","[WtData].[Date].[All].children")))))))

The CUBESETCOUNT returns the maximum number of dates for the SEQUENCE.
The SEQUENCE is generating the rows for the CUBERANKEDMEMBER.
The CUBESET is returning all dates.
UNIQUE and YEAR returns the unique years.
TRANSPOSE spreads the unique years across columns.