Forum Discussion

MikeSlaught's avatar
MikeSlaught
Regular Visitor
10 years ago
Solved

Having trouble viewing data by month in line chart visualization

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, b...
wonga's avatar
wonga
Icon for Continued Contributor rankContinued Contributor
10 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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]))