Forum Discussion

StevenHB's avatar
StevenHB
Regular Visitor
9 years ago
Solved

Dates as Columns

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 d...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
Chihiro
Solution Sage
9 years ago

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.