Rolling up dates in Excel pivot from Power BI data

[mmcanelly]

I've recently started using data sources from Power BI in Excel using the "Pivot from Power BI" feature. I have a date table in the Power BI data which I'm using as a row label in the pivot, and I'm trying to figure out how to roll up the dates to months and years. All of the Grouping features are grayed out, so I can't do it that way.

I know that some of the standard pivot features are limited when using Power BI data, and I'm wondering if this is one of them. Is there a way to roll date fields up to months/years in an Excel pivot when using Power BI data, or is the best way to handle this to just create a month and year column in the underlying table?

[GilbertQ]

Hi @mmcanelly 

What you would need is a date table. You would then create a relationship from your main table to the date table joining on the Date

Here is a blog post on how to create the date table: Power BI Date or Calendar Table Best Method: DAX or Power Query? - RADACAD

[Anonymous]

Hi @GilbertQ ,

I do have the table set up as a date table (by using a continuous date range and setting the "Mark as date table" option), but I'm still not able to roll up the dates. In the link you posted, they have individual dimensions for year, month, etc. in the table. That was sort of my question -- is it required to have everything broken up at that level in the date table in order to allow users to pivot the data in Excel by year and month? Or is there a way to just provide a single date dimension where the user can decide which granularity of time period they want to roll up to (similar to how a standard pivot would work with Excel data).

[GilbertQ · Accepted solution]

Hi @Anonymous 

In the link it is a single table with columns for each date category such as Year, Month, Day and Date

You can then just drag in the year to get the data to aggregate by year.