+ Reply to Thread
Results 1 to 5 of 5

Trailing Twelve Month Date Column

  1. #1
    Registered User
    Join Date
    03-15-2017
    Location
    Long Beach, CA
    MS-Off Ver
    365 Pro Plus
    Posts
    8

    Trailing Twelve Month Date Column

    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!

  2. #2
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    252

    Re: Trailing Twelve Month Date Column

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

    Cheers

  3. #3
    Registered User
    Join Date
    03-15-2017
    Location
    Long Beach, CA
    MS-Off Ver
    365 Pro Plus
    Posts
    8

    Re: Trailing Twelve Month Date Column

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

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    8,293

    Re: Trailing Twelve Month Date Column

    The best I can think of is a work around.
    The formula that populates C5:C15 is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-15-2017
    Location
    Long Beach, CA
    MS-Off Ver
    365 Pro Plus
    Posts
    8

    Re: Trailing Twelve Month Date Column

    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?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1