Pivot Table - Separate columns for Actual and Forecast Value 1

[neukcm]

Hello Everyone,

My first post to this group so please delete if not related.

I’m working on some metrics and would like to know if someone here was able to create separate columns for actual and forecast values in pivot table. See attached image.


Thank you in advance!

 

[SkipVought]

Hi,

Do not understand your question. This looks like you just typed some data into three columns. A PivotTable needs a data source in order to generate a result or even suggest what kind of result could be generated.

Please post a fair representation of your source data that can be copied and pasted into an Excel sheet, showing the relevant fields.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Well I thought about this.

First off, WOW! Are you also under budget? Sounds like Trump: ahead of schedule and under budget! ;-)

I'm guessing that your source data has columns like [highlight #204A87]this[/highlight]...

For any task you have a Forecast Date and an Actual Date for which there may be a date or not.

Since Actual & Forecast can have mutually exclusive MonthYear data, I don't know how you'd get all the MonthYear data into the Pivot Date column and therefore get the correct counts.

So I made my own pivot table using formulae in my first solution and a pivot query as a second solution.

My pivot query results...
[pre]
date Actual Forecast
2020-02 1
2020-03 1 1
2020-04 2
2020-05 2 1
2020-07 1
2021-01 1
[/pre]

My sql...

transform    count(*)
select format(a.dat, 'yyyy-mm') as [date]
from 
(
SELECT `Sheet1$`.Actual as [dat], 'Actual' as [cat]
FROM `C:\Users\Owner\iCloudDrive\et-PivotTable.xlsx`.`Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.Actual<>0)
union 
SELECT `Sheet1$`.Forecast, 'Forecast' 
FROM `C:\Users\Owner\iCloudDrive\et-PivotTable.xlsx`.`Sheet1$` `Sheet1$`
WHERE (`Sheet1$`.Forecast<>0)
) a
group by  format(a.dat, 'yyyy-mm')
pivot    a.cat

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Sorry, uploaded the wrong version of workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[neukcm]

Wow, thank you @SkipVought!! Appreciate your effort Mate, awesome!

I think my questions is to broad, apologies. I have attached snapshot of what I'm trying to achieve using Pivot table or Power pivot.
Here I got the source data and the desired pivot table output. Note, I know power query, power pivot and little knowledge about DAX formula / measures.

Basically I just want separate columns for actual dates (<=reporting month) and forecast dates (>reporting month). So as the cumulative.

Hope this make sense now, if still not clear happy to elaborate further. Just want to automate this! Thank you so much!

Mac

 

[SkipVought]

Here's another shot at your requirements. Workbook attached.

The YELLOW cells indicate cells where the user can enter data to manipulate the pivot. I think I should have put the Reporting Month value on the pivot sheet. I would also add in the Factors sheet a list of dates to use to select Reporting Month in a Data Validation Drop Down list. I'd generate that via a query, as I also would generate a list of Release Codes via a query to make the whole thing more automated.

BTW, a suggestion when you use MS Query in Excel. In order to make the workbook portable, you need a means of changing the connection string to conform with the current workbook's Path and Name, which can be coded...

Dim sPath As String, sDB As String
sPath = ThisWorkbook.Path
sDB = ThisWorkbook.Name

...and substitute these variables in the Connection property and in the FROM statement in the SQL code like this...

sSQL = "SELECT `Sheet1$`.Actual as [dat], 'Actual' as [cat] "
sSQL = sSQL & "FROM `[b]" & sPath & "\" & sDB & "[/b]`.`Sheet1$` `Sheet1$` "
sSQL = sSQL & " WHERE (`Sheet1$`.Actual<>0)"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]