Pivot grouping - only by day not working?

[mra1984]

Hi there,

I am trying to group my pivot data by week - to do this I need to choose days and start/end date. But it's greyed out and not working?

By month/year is working.

The dates are all in dd/mm/yyyy format.

Any ideas?

Thanks

[CK76]

My guess. You are loading your pivot table from data model. For various reasons, it does not allow custom grouping using date range.
If you want to keep using data model, you'd want date dimension table in data model to be used as relationship table (for grouping purpose) and add appropriate measures.

Otherwise, if you bring in data to Pivot Table using Excel Table/Range without going through data model. You'll have option to customize date grouping.

Edit: Is your profile correct and that you are using Excel 2007? If so, above is not the case, and I can't help you. If it is later version of Excel, please update your profile.

[mra1984]

Thanks. That is right; I am using data model. I would like to keep using this (due to the transforming I am doing on the data). So how do I add a date dimension table to it? It has the data stored as dd/mm/yyyy in the data model already.

[CK76]

You need separate date dimension table and build relationship in the model.

How is data brought into data model? Is it directly in PowerPivot? Or through PowerQuery/Get & Transform?

If former, you'll need to build date dimension table in Excel sheet, and load that into data model.
If latter, you can use M to dynamically create date dimension table based on date range present in data set.

Sample M, with manually supplied Start date for single year, assuming Sun-Sat week.

Please Login or Register  to view this content.

If you need further help, I'd need you to supply sample workbook with enough data to demonstrate your need.

Edit: StDate is parameter value, set to "1/1/2018".

[mra1984]

Hi - I have added a time dimension into the model and also built relationships to it.

However, I can only still group by days and the start/end date and number of days to group is still greyed out.

See attached.

Thanks again

[CK76]

Few things.
1. You should append 2018 data to 2017 table in PowerQuery before loading to data model.

2. You don't have Weeknumber column in dimension table. This is required to align each year's data on weekly basis.

3. Since your raw data is already aggregated at week level, you should not use date or Month level aggregation (it will never align well and will be misleading).

4. Don't use attribute generated grouping. Rather use column from dimension table for your row field.

I'm bit busy, but will see if I can find time to modify your model.