Forum Discussion

gogrizz's avatar
gogrizz
Icon for Advocate I rankAdvocate I
4 years ago

Imitate Date Hierarchy on Line Chart

I currently have my PBIX file setup to use the Auto Date/Time intelligence.  For a few different reasons, I need to uncheck the "Auto Date/Time".  That gets rid of the date hierarchy with year, quarter, month, day.   I had to make some DAX adjustments after unchecking the "Auto Date/Time", but the one thing I can't figure out is the charts.  WITH the auto date/time intelligence enabled, I have something that looks like this in several spots:

 I have bookmarks setup so the end user can easily navigate to see the washes per month, quarter, and year - all laid out using the date hierarchy.  WITHOUT the auto date/time intelligence, I can't figure out how to replicate this.  I've tried a number of different ways that I can't really explain becuase it's more like random attempts than actually understanding what I'm doing.

 

The Y-axis is a measure that counts number of washes that occured in whatever timeframe that's displayed on the X-axis.  The X-axis I have no idea what to do.  I got close a few times but the formatting is ugly (not like what I have displayed above) and when I get close to showing "Washes per Month", the x-axis is huge because it doesnt' group things together like the date hiearchy does.

 

Thanks for any help or suggestions!

Need Help

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    You should create a Calendar Table with calculated column formulas for Year, Quarter, Month name and Month number.  Sort the Month name by the Month number.  To your visual, drag any date dimension from the Calendar Table.  Ensure there is a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table.

  • gogrizz's avatar
    gogrizz
    Icon for Advocate I rankAdvocate I
    4 years ago

    Thanks for your reply - I'm still not able to get the visual that I'm looking for (the one I've showed in the original question).  It shows the progress of a company, broken down in a month-by-month basis.

     

    When I sort by month, I see all the events that happened in January, February, March, etc. but it doesn't roll chronologically.  It takes all events from January 2014, January 2015, January 2016 etc.  and looks like this:

     

    If I'm using the Auto Date/Time intelligence, what I'd do is put the date and month field in the X-Axis, then expand down one level in the hierarchy to get the graph that I show in the original post.  When I do that now, it separates it out into categories instead of being "continuous" and looks like this with a huge scroll bar at the bottom

     

    What am I missing?

     

    Thanks again!