Excel's Pivot Table's Calculated Field with Date operations always return 0

Question

I have a Pilot Table which summarizes a lot of operations.

Each operation is dated:

Data       | Operation | Result
-----------|-----------|-------
01/09/2020 | A         | 3
05/10/2020 | B         | 4
07/11/2020 | C         | 5
13/11/2020 | B         | 3
20/11/2020 | B         | 8
01/12/2020 | C         | 3
01/12/2020 | A         | 9

So I have a column with a start date [ MIN( Date ) ] and final date [ Max( Date ) ]:

Operation | Start Date | End Date 
----------|------------|------------
 A        | 01/09/2020 | 01/12/2020
 B        | 05/10/2020 | 20/11/2020
 C        | 07/11/2020 | 01/12/2020

Now I need a column that returns the period (days) from start date to end date.

Using =DATEDIF( MIN(DATE), MAX(DATE), "D" ) in the Calculated Field Formula does not work. I would guess that's because the Max and Min dates are not in the same line.

I cannot use the start date and end date either as they calculated in the "Sum Values" field box.

I am probably missing some important concept here. Thanks

Answer 1

You can accomplish this easily in Power Query (Get & Transform) available in Excel 2010+

With you cursor in the table, navigate to:

• Data=> Get & Transform => from Table/Range
• The Power Query UI will open, with the data table loaded.
• Change the Type of the date column from datetime to date
• Then select the Operation column and
  • Group by
    • Advanced
    • Add aggregations of Min and Max for the start and end dates

• Add a custom column with the formula =Duration.Days([End Date]-[Start Date])

• Close and load

MCode

et
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {" Operation ", type text}, {" Result", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {" Operation "}, {{"Start Date", each List.Min([Date]), type nullable date}, {"End Date", each List.Max([Date]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Duration", each Duration.Days([End Date]-[Start Date]))
in
    #"Added Custom"