Pivot Table with Multiple Dates

latest status 2023/6/15？

create temp table aa as
select f01,f02,f03,f04,sum(f05) total,max(f02) m from showing_the_latest_Status group by f01;
cli_no_header;
select colExclude[m] from aa;

DeejayJohn 

Depends on your Excel platform/version. Creating PivotTable you may add data to data model, add DAX measures like

Arrival:=MIN( daysConsumed[ARRIVAL DATE] )
Departure:=IF( "Active" IN VALUES(daysConsumed[STATUS]), BLANK(), MAX(daysConsumed[DEPARTURE DATE]) )
ManDays:=SUM( daysConsumed[MAN-DAYS] )
Current Status:=IF( "Active" IN VALUES(daysConsumed[STATUS]), "Active",  "Inactive" )

and use them creating PivotTable

Hi DeejayJohn,

You can try these steps to make a Pivot table with multiple dates:

Step 1: Create a new pivot table

1. Select the data that you want to include in the pivot table.
2. Click the Insert tab.
3. In the Tables group, click PivotTable.
4. In the Create PivotTable dialog box, select the New Worksheet option.
5. Click OK to create the pivot table.

Step 2: Add the required fields to the pivot table

1. Drag the NAME field to the Row Labels area of the pivot table.
2. Drag the STATUS field to the Filters area of the pivot table.
3. In the Filters area, click the All checkbox next to the STATUS field.
4. Clear the checkbox next to the Inactive status.
5. Click OK to apply the filter.
6. Drag the ARRIVAL DATE field to the Values area of the pivot table.
7. Change the value calculation for the ARRIVAL DATE field to Maximum.
8. Drag the MAN-DAYS field to the Values area of the pivot table.
9. Change the value calculation for the MAN-DAYS field to Sum.

Step 3: Calculate the total number of man-days consumed to date

To calculate the total number of man-days consumed to date, you can use the following calculated field:

Total Man-Days Consumed to Date =
IF([STATUS] = "Active", [MAX of ARRIVAL DATE] + [MAN-DAYS], [MAN-DAYS])

This calculated field will return the total number of man-days consumed to date, even for workers who are still active.

To add this calculated field to the pivot table, follow these steps:

1. Click the PivotTable Analyze tab.
2. In the Calculations group, click Calculated Field.
3. In the Calculated Field dialog box, enter the following information:
   • Name: Total Man-Days Consumed to Date
   • Formula: IF([STATUS] = "Active", [MAX of ARRIVAL DATE] + [MAN-DAYS], [MAN-DAYS])
4. Click OK to add the calculated field to the pivot table.
5. Drag the Total Man-Days Consumed to Date field to the Values area of the pivot table.

Step 4: Remove the duplicate rows

To remove the duplicate rows from the pivot table, you can use the following filter:

1. In the PivotTable Analyze tab, click the Filter button.
2. In the Filters pane, click the Total Man-Days Consumed to Date field.
3. In the Filter dialog box, click the Unique Values checkbox.
4. Click OK to apply the filter.