Hi, I'm new to DAX, but I'm sure the solution here should be relatively easy? Any elegant solution gratefully received. I need to be able to report where, between 2 Forecast versions, the Closure date for a Project has slipped (or accelerated). I've used 'Calculated Items' within an ordinary Pivot but there are drawbacks within it. Perhaps DAX can help?
SOLUTION REQUIRED & NATURE OF DATA
I need my Pivot to only have 3 columns: Project Closure Date (Period A forecast), Project Closure Date (Period B forecast); Days difference (and where I can use the Pivot to filter out Days difference = '0'; and where the calculation returns a message if the Closure Date was Blank in one column, so the calculation is invalid)
I have Unpivoted Excel data, one row for for each Project/Month. Each row has columns showing
- STRING: Project Code
- NUMBER: the Efficiency Project forecast £ monetary saving that month (varies)
- DATE: the Project's Closure date (repeated on each row)
- STRING: The forecast Version number (eg P6 = Period 6, P10 = Period 10) Each month's forecast has around 25k rows of data (around 1,200 Projects, so, on average, 21 unique month rows for each project).
**RAW DATA FORMAT**: - I've uploaded an image to this post. This is an excerpt of data (showing historic Projects for data protection purposes). I have appended just 2 Forecasts (i.e. Versions) for visual simplicity. I can either Append each month's forecast on 1 Data Table or have separate data tables in the Data Model for each month's forecast (version).
CONSIDERATIONS are:
a) I can't use DATE DIFF: as I'm comparing dates in the same underlying data field ('Closure'), not 2 different underlying date fields (eg. Invoice posted date, Invoice paid date).
b) 'Closure' date is repeated on several rows for each Project
c) Varied Data Types: (DATES and NUMBER (for number of days variance in date)
d) Multiple Forecasts: My raw data uploaded shows just 2 Lots of data (Period 6 and Period 10 forecasts, each of around 25k rows of data). However, I'd like to be able to dynamically vary which Periods that I compare, up to say between 10 to 15 "versions".
ATTEMPTS SO FAR & ISSUES:
1. PIVOT WITHOUT DAX (using 'Calculated Items'):
- Method: I put a new column in the underlying data to identify the first data line of each Project, so I only have 1 Date cell, avoiding the need to use the AVERAGE function (which doesn't work when then using 'Calculated items')
- Problems 1: I can't filter on the 'Calculated Items' Field (Days variance) - is that just me?
- Problem 2: The Pivot 'Value Field settings' makes all 3 columns in the pivot be either in DATE format (not suitable for Days Variance column), or NUMBER format (not suitable Closure Dates columns by Version)
2. DAX PIVOT
I can't get the correct code for the variance. It subtracts the same Version Closure Date away from itself, so resolves to Zero.
Thanks!!!
SOLUTION REQUIRED & NATURE OF DATA
I need my Pivot to only have 3 columns: Project Closure Date (Period A forecast), Project Closure Date (Period B forecast); Days difference (and where I can use the Pivot to filter out Days difference = '0'; and where the calculation returns a message if the Closure Date was Blank in one column, so the calculation is invalid)
I have Unpivoted Excel data, one row for for each Project/Month. Each row has columns showing
- STRING: Project Code
- NUMBER: the Efficiency Project forecast £ monetary saving that month (varies)
- DATE: the Project's Closure date (repeated on each row)
- STRING: The forecast Version number (eg P6 = Period 6, P10 = Period 10) Each month's forecast has around 25k rows of data (around 1,200 Projects, so, on average, 21 unique month rows for each project).
**RAW DATA FORMAT**: - I've uploaded an image to this post. This is an excerpt of data (showing historic Projects for data protection purposes). I have appended just 2 Forecasts (i.e. Versions) for visual simplicity. I can either Append each month's forecast on 1 Data Table or have separate data tables in the Data Model for each month's forecast (version).
CONSIDERATIONS are:
a) I can't use DATE DIFF: as I'm comparing dates in the same underlying data field ('Closure'), not 2 different underlying date fields (eg. Invoice posted date, Invoice paid date).
b) 'Closure' date is repeated on several rows for each Project
c) Varied Data Types: (DATES and NUMBER (for number of days variance in date)
d) Multiple Forecasts: My raw data uploaded shows just 2 Lots of data (Period 6 and Period 10 forecasts, each of around 25k rows of data). However, I'd like to be able to dynamically vary which Periods that I compare, up to say between 10 to 15 "versions".
ATTEMPTS SO FAR & ISSUES:
1. PIVOT WITHOUT DAX (using 'Calculated Items'):
- Method: I put a new column in the underlying data to identify the first data line of each Project, so I only have 1 Date cell, avoiding the need to use the AVERAGE function (which doesn't work when then using 'Calculated items')
- Problems 1: I can't filter on the 'Calculated Items' Field (Days variance) - is that just me?
- Problem 2: The Pivot 'Value Field settings' makes all 3 columns in the pivot be either in DATE format (not suitable for Days Variance column), or NUMBER format (not suitable Closure Dates columns by Version)
2. DAX PIVOT
I can't get the correct code for the variance. It subtracts the same Version Closure Date away from itself, so resolves to Zero.
Thanks!!!
