Having trouble viewing data by month in line chart visualization

[MikeSlaught]

Hello there! I am trying to do something that I thought would be very simple.

Here I have my table:

I want to have a line chart that displays a count of transactions not only by year, but also by month. However, it ONLY gives me the option to view by year.

I'm going crazy trying to figure this out.... some insight would be greatly appreciated!

Thanks for your help,

    Mike

[wonga]

@MikeSlaught

It looks like you date hierarchy is missing levels like day and month. See if there's anything you can do in the "Edit Queries" windows to manipulate the dates such that that you can get that desired hierarchy.

[KHorseman]

This kind of thing is best handled by a date table. You would create a relationship between the date table's date column and the Trans Date*, then use columns from the date table as the axis hierarchy.

A date table is used for time intelligence functions and should contain a date column with a continuous range of dates that cover the entire period that your data model will use, with no skipped dates. Usually they also contain other columns with supplemental breakdowns of those dates (week, month, quarter, etc). There are some out of the box that you can use I think from azure services or you can find custom queries for creating your own. I have posted an example query before, but I don't remember in which thread.

*for Post Date you would then have to create a second relationship to that date table date column. This will be an inactive relationship, so you'll need to create a measure specifically for amount by post date.

Amount By Post Date =CALCULATE(SUM(Table[Amount]), USERELATIONSHIP(Table[Post Date], DateTable[Date]))

[Sean]

@MikeSlaught You can read more about what @KHorseman is suggesting go here...

http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

[MikeSlaught]

Thanks, I was hoping I would not have to set something like this up, but it seems it is the only way....

Much appreciated, I'll mark as solution once I get it to work

[KHorseman]

I found that example date table query. It's just an example but it might work for you. The first statement (#"Created Date List") is where you set the range of dates. Enter the first date and the number of days forward from that date there.

[MikeSlaught · Accepted solution]

The problem lied with heirarchy functionality being currently disabled for Line Charts:

http://community.powerbi.com/t5/Desktop/Date-Hierachy/m-p/13665#M3481

Thanks for your help!