Trailing Twelve Month Date Column

[cbrown6305]

Hello,

I have a pivot table with dates in the columns bucket. I know I can group columns into quarters and years, but I am looking to make a third grouping of a specific period, that is, the trailing twelve months ended September 30, 2018. Ideally, My table would have three colums: 2016, 2017, and Trail Twelve Months Ended September 2018.

FYI - I plan to use this with both regular and power pivots.

Thanks!

[FoxSeaLady]

Hi
Do you have a sample spreadsheet you can upload. Even just the data layout will help.

Cheers

[cbrown6305]

Attached. The pivot table on the first worksheet pulls from the data model, but the data is also in the second worksheet.

[JeteMc]

The best I can think of is a work around.
The formula that populates C5:C15 is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[cbrown6305]

After a considerable amount of research, I was able to get a 2017 ("FY17") and Trailing Twelve Month ("TTM18") column to populate using DAX measures in the excel data model. Here was my method:

1. Create a separate calendar table with three columns: Date, FY17, and TTM18. Group dates into FY17 and TTM18 by using a "Y" or "N" for each date in those columns.
2. Create a relationship between the dates in my SalesData table and the dates in my Calendar table.
3. Add the following DAX measures to the SalesData table:
FY17 Sales:=CALCULATE(SUM('SalesData'[Total Price Paid]),'Calendar'[FY17]="Y")
TTM18 Sales:=CALCULATE(SUM('SalesData'[Total Price Paid]),'Calendar'[TTM18]="Y")

So now I can drop in a pivot that as the following columns: Item, FY17 Sales, TTM18 Sales.

I am now facing another issue though. I want to have columns for every month and FY17 and TTM18 columns at the end; however, when i drop "dates" as a column and "Total Price Paid" as a sum item, the pivot table triplicates the data (because all transactions are also associated the "FY17" and "TTM18" fields). Anybody know a way around this?