May 07 2019 02:09 AM
May 07 2019 02:09 AM
I am working on my departments budget, and wish to show the costs per month. However, invoices are registered across the month (on 02/01/2019, 05/01/2019, 13/01/2019, ...). Now, the aim is to show per month the costs and not per date it is registered.
- I triple checked that the date field is indeed a date (I can change the date to any date format I'd like)
- I even can use a 'date' timeline
However, when I click on group like here: https://support.office.com/en-us/article/group-or-ungroup-data-in-a-pivottable-c9d1ddd0-6580-47d1-82...
- When I select one date, I see "cannot group"
- when I select all dates, it just group the dates
* I restarted Excel
* I verified that the group is date
* I updated to the latest version of excel
May 07 2019 03:15 AM
So you have your pivot set like this and then right click on date and you don't get the Group Option?
May 07 2019 04:06 AM
@Wyn Hopkins I get the group option, but once I click it, it tells me 'cannot group that selection'
May 07 2019 09:56 AMSolution
May 08 2019 12:30 AM
@Detlef Lewin I was trying to apply your solution, when suddenly the pivot table itself divided my date into months:
The problem is, I have no idea how I did this. the original table only has 'Date' (not months). He added the field 'month' himself. It is perfect, because this is exactly what I need. (with this, I don't need to group). However, how did I do this? how do I replicate this?
May 08 2019 12:36 AM
May 08 2019 12:45 AM
@Wyn Hopkins true true, I can ungroup it. -- and I can group it. Sweet. How does that make sense?
well, no problem, it works :) (could it be that it is because I changed the format of the date column? first it was dd-mm-yyyy and then I changed it to dd.mm.yyyy (to see if everything would change, and everything indeed did)).
oh well, thank you so much for the help.
May 08 2019 01:10 AM - edited May 08 2019 01:11 AM
Great, so are you using the Power Pivot data model?
May 08 2019 03:04 AM
@Wyn Hopkins I have no idea what the Power Pivot data model is, but I'm googling it right now.
Aug 17 2021 03:40 PM
@SBiebaut86 I was having the exact same issue and suddenly it started working after I changed my date format to M, so I guess that's the work around.