Can I create these charts by using a PivotChart technique

[Pete Mccann]

Dear all. In the attached example file, I have a list of documentation to be delivered on a project. For each document I have a Baseline date (the original expected date); a Forecasted Date (today's currently expected delivery date) and an Actual Delivery date. I can create the charts I need (as shown in the file) but I am wondering if I can create the same charts by using a PivotChart technique. A PivotChart would be very useful on the actual dataset which is much larger than this example. The first chart is a snapshot of the status and the second is a cumulative total "S-curve". I have used random dates in the table. Thanks in advance.

 

[Chihiro]

I'd advise against using Datevalue(Text()) function. It will only work if receiving end has same regional setting as you do.

For an example, I have US setting and this will result in calculations returning #VALUE! error.

As for your question, it is possible. But will largely depend on your Excel version what methods are available.

Few methods.

1. PowerQuery - Perform necessary calculation within PQ and the load to Pivot table.

2. PowerPivot - Add table to data model (through PQ), then add necessary measures. Load to Pivot table.

3. Load table to array, create another array to serve to hold calculation result. Load result array to model.

First two methods requires Excel 2010 or later (specific SKU if 2013). Last one I believe requires 2013 or later.

Alternate method is to add helper columns to source table.

But from your sample, it isn't clear what logic/calculation is used for baseline/forecast.

 

[Pete Mccann]

Thanks for your comments Chihiro.
Regarding Datevalue(Text()), is there an alternative Excel function that would allow us to keep the structure of this calculation? If the format of the Datevalue(Text) function ["dd/mm/yyyy"] is the same as the format of the Start and End dates, is it OK? I normally share my spread sheets with UK and French users.

I am using Excel 2016 but I have not had any experience with any of the techniques in your 1., 2., or 3. bullets above.

Regarding the "random" dates, I'm sorry - this was an error on my part. I was experimenting with random dates to see if both charts updated correctly. In practice, we would receive date information for Baseline and Forecast activities and we would enter (manually) the Actual dates as they were achieved. Essentially, at the start of the project we would calculate the baseline dates based on a Plan of Work at that time. As the project progresses we would develop various forecast dates. When we get achieved dates, these get entered into the table.

 

[Chihiro]

Regarding Datevalue(Text()), is there an alternative Excel function that would allow us to keep the structure of this calculation

If dates are stored as actual date (and not TEXT), then there is no need for additional calculation layer.
Ex: In J6
=SUMPRODUCT(($B$6:$B$133>=$H6)*($B$6:$B$133<=$I6))

Excel will automatically accommodate for system/Excel region setting.

Ok, so there is no calculation logic needed for dates. That makes it easier.
You will have to flatten table (i.e. single date column) and then base your pivot table off of flattened table.

If you can upload sample file that more accurately represent your actual data. I can do a sample using PowerQuery method.

 

[Pete Mccann]

Thanks. I am attaching an updated file that incorporates the change to the Sumproduct calculation above and also has fixed dates. I have not done anything to "Flatten" the table yet.

 

[Chihiro]

Sorry for late reply. I was sick yesterday.

See Pivot sheet for sample.

1. Select the table, go to Data tab and "From Table/Range"
2. Select date columns and replace null with 1/1/1900

3. Unpivot date columns and filter out 1/1/1900. Then change Value column to Date type.

4. Create date dimension table. Open blank query and go to View->Advanced Editor. Paste in following code.

let
    Source = List.Dates(StDate, Duration.Days(Duration.From(#date(Date.Year(StDate)+3,1,1)-StDate)), #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Year", each Date.Year([Column1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each Date.Month([Column1])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Column1])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "OrdinalDate", each Duration.Days(Duration.From([Column1]-StDate+#duration(1,0,0,0)))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Weekday", each Date.ToText([Column1],"ddd","en-US")),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Quarter", each "Q" & Number.ToText(Number.RoundUp([Month]/3))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom5",{{"Column1", "Date"}}),
    #"Added Custom6" = Table.AddColumn(#"Renamed Columns", "Year_Month", each Text.From([Year]) & "-" & Text.PadStart(Text.From(Date.Month([Date])),2,"0")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom6",{{"Date", type date}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Day", Int64.Type}, {"OrdinalDate", Int64.Type}, {"Weekday", type text}, {"Quarter", type text}, {"Year_Month", type text}})
in
    #"Changed Type"

5. In home tab, add Parameter. "StDate" with value 1/1/2018.
6. "Close & Load To"->Only Create Connection with "Add this data to the Data Model".


7. Back in Excel, Data -> Data Tools -> Relationships.
Use Value column from Table1 as foreign, and Query1's Date column as Primary.

8. Insert Pivot (using data model as source). Add Year_Month as row, Attribute as Column. Then add Count of Title as value field. And set it to show Running Total In -> Year_Month

You can see steps taking in PQ by going to editor and looking at applied steps.

If you want to do Documentation Delivery from Pivot, you'll need to split original table1 into two queries (1 without unpivot, and other as above).

 

[Pete Mccann]

Thank you Chihiro. Phew.....This is a tremendous amount of work you have done. I will need some time to try to understand the steps and the logic. This is way beyond what I have done so far in terms of Power Query / Power Pivot. I have tried to recreate the steps you took above and I got to Step 4 before I stumbled on trying to create a blank query....I will continue though because the concept of the Power Query / Power Pivot is very interesting. Again, many thanks.