Need Assistance Writing DAX Expression for Dynamic Column Creation
-
Wednesday, January 18, 2012 5:47 PM
I've been playing with this for the better part of a day and I'm stumped. For ease of understanding let's say I have a Sales PowerPivot table that has a date column and an Item column. Every time an item sells it was added to the table that is the basis of the PP with the 1st day of the month it was sold. As an example, this is what the top 10 rows look like:
Item Date cookie 1/1/2010 book 1/1/2010 book 1/1/2010 card 2/1/2010 cookie 2/1/2010 card 2/1/2010 card 2/1/2010 book 2/1/2010 cookie 2/1/2010 cookie 2/1/2010
Lets say I have 10,000 records that go from 1/1/2010 thru 12/1/2011. I wanted to create a chart from this data that ONLY shows the last 6 months of data summarized by the count of items sold and the month they were sold in. I also wanted this chart to show me 6 months of forcasted sales based on the 6 months displayed.
The first thing I did was create a pivot table based on the powerpivot with dates as columns and items rows. I then realized I needed totals by month for the forecast, but pivot charts can't chart the grand total columns. I took the approach of having a separate area on the Excel worksheet reference a cell in the Pivot Table. As I said above I only wanted to chart the previous 6 months and 6 months in the future. I could take the approach that every month I refresh the data and then change the data refernces for the chart, but that is a lot of work. I looked at the DAX functions and found that if I use the following syntax as a measure in my values section, only the last 6 months of data would be in the pivot table and therefore my excel references would always point to the correct PP cells and the chart would show what I want (note: that I added a column with a 1 in it to the table so I can use the sumx function):
=SUMX(FILTER(Raw_Data, Raw_Data[Filter_Date] >= datevalue("7/1/2011"),Raw_Data[RecordCount])
However I want the table to by dynamic, I don't want to have to open the measure every month and change the date. So I have tried a bunch of different things. I tried creating a table that had 1 value and use that in a DATEADD function, but the result set was all the records:
=SUMX(FILTER(Raw_Data, Raw_Data[Filter_Date] >= DATEADD(DISTINCT(Collection_Date[Filter_Date]),-12,MONTH ) && Raw_Data[Change_Type]="Non Directed Changes"),Raw_Data[RecordCount])
I tried using DATEADD(DISTINCT(TODAY(),-12,MONTH ) in the above example but got the following error:
The Boolean expression does not specify a column. Each Boolean expressions used as a table filter expression must refer to exactly one column.
I've tried other options as well but am stymied. How can I create a pivot table that ONLY shows the last 6 months of data regardless of the calander date?
