Using array functions with a data model

surma3

New Member
Joined
Feb 7, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,693
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 7, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,693
Office Version
  1. 365
Platform
  1. Windows
The only thing that I can see which might have even a remote chance of doing what you want would be something like
Excel Formula:
TRANSPOSE(UNIQUE(YEAR(CUBESET("WtData","[Date]",0))))
Although I'm not entirely convinced that it will work.
 

surma3

New Member
Joined
Feb 7, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,307
Messages
5,641,440
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top