none
Timescale in PivotTable : Weeks or Months instead of Days RRS feed

  • Question

  • Hello,

    I built a Pivot Table with OData queries. It works fine but the Timescale is always in Days. How can we change that unit?

    Thanks for your help

    Monday, January 9, 2017 5:14 PM

Answers

  • Week could be something as simple as right clicking on "TimeByDay | transform | week | start of week" or whatever you like.

    Just remember as I said above, when your week crosses into a second month or year, your summaries will start to look odd if you have more than one column field, such as Month and below that, week.

    Since our time periods start on a Monday, I changed the first day of the week to Monday from its default of Sunday so the data would line up. You'd go into the advanced editor and add in Day.Monday as the optional start of week, something similar to below

    #"Calculated Start of Week" = Table.TransformColumns(WhateverYourSourceIs,{{"TimeByDay", each Date.StartOfWeek(_,Day.Monday), type datetime}}),


    Ian


    • Edited by Ian Bruckner Tuesday, January 10, 2017 4:32 PM
    • Marked as answer by WLID1966 Tuesday, January 10, 2017 6:46 PM
    Tuesday, January 10, 2017 4:30 PM
  • Right click on your query, such as from "Workbook Queries", select "Load To" and check the box for "Add this data to the Data Model". Then be sure your pivot table is using the data model as the source for the pivot table. If you previously only created a connection or loaded it to a table in your workbook and built a pivot table from it, it will be sourced from that. You'll be able to see by looking at the large box in the PivotTable Fields selector.

    Ian

    • Marked as answer by WLID1966 Tuesday, January 10, 2017 6:46 PM
    Tuesday, January 10, 2017 4:37 PM

All replies

  • When I add TimeByDay into a pivot table, it auto creates elements called Month, Quarter, and Year for me. Is your excel reasonably up to date with patches and do you load the data to the data model? I'm not sure what causes them to auto create, only know that it seems like they always do.

    I also add "week" myself to show data within the same time periods as we define timesheets. That gets a little screwy when a timesheet period week crosses to a second month or year, so be mindful.


    Ian

    Tuesday, January 10, 2017 4:21 PM
  • Week could be something as simple as right clicking on "TimeByDay | transform | week | start of week" or whatever you like.

    Just remember as I said above, when your week crosses into a second month or year, your summaries will start to look odd if you have more than one column field, such as Month and below that, week.

    Since our time periods start on a Monday, I changed the first day of the week to Monday from its default of Sunday so the data would line up. You'd go into the advanced editor and add in Day.Monday as the optional start of week, something similar to below

    #"Calculated Start of Week" = Table.TransformColumns(WhateverYourSourceIs,{{"TimeByDay", each Date.StartOfWeek(_,Day.Monday), type datetime}}),


    Ian


    • Edited by Ian Bruckner Tuesday, January 10, 2017 4:32 PM
    • Marked as answer by WLID1966 Tuesday, January 10, 2017 6:46 PM
    Tuesday, January 10, 2017 4:30 PM
  • Thanks Ian

    How do you load the data to the data model? 

    Tuesday, January 10, 2017 4:32 PM
  • Right click on your query, such as from "Workbook Queries", select "Load To" and check the box for "Add this data to the Data Model". Then be sure your pivot table is using the data model as the source for the pivot table. If you previously only created a connection or loaded it to a table in your workbook and built a pivot table from it, it will be sourced from that. You'll be able to see by looking at the large box in the PivotTable Fields selector.

    Ian

    • Marked as answer by WLID1966 Tuesday, January 10, 2017 6:46 PM
    Tuesday, January 10, 2017 4:37 PM
  • Hello Ian,

    Question of a beginner:

    In which view are you working to do that: PowerPivot ? PowerView? PowerQuery? or are you using Power BI ?

    Thanks


    • Edited by Robert6156 Wednesday, January 11, 2017 5:31 PM
    Wednesday, January 11, 2017 5:28 PM