Dates as Columns

[StevenHB]

I'm importing data into PBI using the Project Web Access (PWA) OData data sources.  One of the pieces of information is the time reporting period end date, which I've configured as a column. When I drill down to the period level, I end up with a display like the following (the first row is a summary for a person - I've cut the screenshot to eliminate confidential information):

Those are day-of-month numbers in the heading labels (and, it turns out, they're for different months - they're sorted ascending as numbers from left to right). Just for clarity, here's the view when I drill up one level on the column:

I can tell by looking at other data that April-July is for 2017 while September-December is for 2016.  Clearly, the field is recognized as a date. Note that there are months in this view but no year indicated. Is there some way to force PBI to display the full period ending date in the first view and the year in this second view without losing the ability to drill up and down? Or do I need to create a text version of the period end date as a calculated field in a format like 2017-07-15 (YYYY-MM-DD), which will eliminate desired drillabality?

[Anonymous · Accepted solution]

@StevenHB.

What is the format of data values in “period end date” column? If the data values are formatted as YYYY-MM-DD and you set the data type of “period end date” field to Date,  after you drag the “period end date” field to Matrix column, please ensure that you choose “Date Hierarchy” for “period end date” as shown in the following similar screenshot, and you can delete “Quarter” from the Hierarchy if you don’t need the Quarter level.

And when you use drill down feature in the Matrix visual, you can choose the “Expand all down one level in the hierarchy” button to expand details with Year label.

Regards,
Lydia

[Chihiro]

You should be able to create date dimention table (dynamic or otherwise).

Create relationship with your period end date column.

Then create custom hierarchy as needed. My Quarter & Month columns are concatenated with Year value (ex. Q1 2017).

You can then use this hierarchy in your report to control drill down.