locked
Data Model Calendar has Fiscal Month field, but display Month name RRS feed

  • Question

  • I was using a shortcut involving TOTALYTD to sort my pivot table by Fiscal Month, but I noticed this caused some issues when trying to drill down.  I added a column to the Calendar date table in the data model to hold the Fiscal Month and the Fiscal Year.

    If I add this to the Pivot Table it shows the data in the desired order.  I want it to be ordered this way but display the month name instead of the Fiscal Month.  Is there a way to do this?  I tried looking for an option to alter the value but the only thing I found close doesn't allow you to enter a formula or statement.

    Wednesday, February 26, 2020 3:21 PM

Answers

  • Thanks Herbert.  I think I found a simpler solution (for my issue), and that was to use the Sort by Column in the Power Pivot Data Model.  I had added a Fiscal Month to be able to sort so the starting month was July (not January) but it was displaying the number 1 (for July).  I found another post that said to select the month and click the Sort by Column option.

    Then, select FiscalMonth as the sort option.

    This allows you to use the Month column that was already in the table but display it in order of Fiscal Month.

    Friday, February 28, 2020 7:43 PM

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Generate Fiscal Year, Fiscal Month and Fiscal Quarter as text in PQ.
    Load query into PP Model as Date Table.
    Start of Fiscal Year month selectable with Slicer.
    http://www.mediafire.com/file/j2ze5l77myrvaaa/01_06_20.xlsx/file


    Thursday, February 27, 2020 8:13 PM
  • Thanks Herbert.  I think I found a simpler solution (for my issue), and that was to use the Sort by Column in the Power Pivot Data Model.  I had added a Fiscal Month to be able to sort so the starting month was July (not January) but it was displaying the number 1 (for July).  I found another post that said to select the month and click the Sort by Column option.

    Then, select FiscalMonth as the sort option.

    This allows you to use the Month column that was already in the table but display it in order of Fiscal Month.

    Friday, February 28, 2020 7:43 PM