Forum Discussion

jnixon's avatar
jnixon
Icon for Advocate II rankAdvocate II
6 years ago
Solved

Set Chart X-Axis End Date to Today

Greetings all, It seems Power BI charts can auto-scale the axes or you can specify a Start and/or End.  I'd like to set the end date of my X-Axis to Today - is there a way to do this?  Ideally witho...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    Hi jnixon ,

     

    Maybe you can do like this.

    1. Create a calendar table.

    Calendar = 
CALENDAR(
    MIN( 'Table'[Date]),
    TODAY()
)

    2. Add the [Date] column of the calendar table to the visual and Set it to "Categorical" value.

    3. Check this option.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

jnixon's avatar
jnixon
Icon for Advocate II rankAdvocate II
6 years ago

Sorry, I think I'm not being clear.  I don't need to filter out the future dates on the axis.  My problem is the opposite - I want the axis to run until today, even if there is no data today.

 

So if a customer stopped ordering in December, the X-axis only goes to December, UNLESS I manually put a more recent date into the Format/X-axis/End setting.   But if I put today's date into that setting and the customer orders tomorrow, then tomorrow's order will not display on the chart. 

 

So is there a way to dynamically set the X-axis/End attribute to Today()?  I still can't figure it out.  The suggestions are much appreciated, but they are not solving the problem.  Any other ideas?

v-lionel-msft's avatar
v-lionel-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi jnixon ,

 

Maybe you can do like this.

1. Create a calendar table.

Calendar = 
CALENDAR(
    MIN( 'Table'[Date]),
    TODAY()
)

2. Add the [Date] column of the calendar table to the visual and Set it to "Categorical" value.

3. Check this option.

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • jnixon's avatar
    jnixon
    Icon for Advocate II rankAdvocate II
    6 years ago

    Thanks Lionel, I would say that is a partial solution, so Kudos!

    But if you use the new Calendar[Date] field in the visual, then how can we use the visual at a YYYY-MM or YYYY level?  This is a common manipulation of a time-based visual.

    Thanks again!

    Jeff

    • jnixon's avatar
      jnixon
      Icon for Advocate II rankAdvocate II
      6 years ago

      Actually, your solution helped me realize that your suggestion can work without a new calendar table, which is much better to keep a simple model.  My ignorance of finding the "Show items with no data" was the problem.  My Dim_Date table has dates into the near future, so all I had to do was use the existing date table, set the "Show items..." property, and use a Relative date filter on the page.  Worked like a charm.  So I will say you did in fact propose a solution, but it did not have to be quite so complex.  Probably you assumed that my Date table did not include up to today and that I already know about the "Show items..." property.

      Thanks again,

      Jeff