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, 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

 

6 Replies

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