Set Chart X-Axis End Date to Today

[jnixon]

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 without a complicated DAX workaround, but in any case...

Thanks,

Jeff

[v-lionel-msft]

Hi @jnixon ,

1. Set the end date manually

2. Set dynamic end date

(1) You can use DAX to achieve this, like this.

Measure = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table', 'Table'[Date] <= TODAY()
    )
)

(2) You can add filter on this visual.

How to do it depends on your needs.

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]

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 · Accepted solution]

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]

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]

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  

[Greg_Deckler]

Several ways. Easiest would probably be:

Measure = TODAY() - MAX('Table'[Date])

Filter on this >= 1

[jnixon]

Thanks Greg, but I think I'm not fully understanding.  You want the measure to return an integer?  If so, then i had to wrap it in a Convert(), then I dragged the measure to the filter pane for the visual and set to >=1.  Seemed to have no effect on the visual.

I have a Fact_Orders table and a related a Dim_Date table (which runs through year-end).  If I'm plotting orders by date and filtering on a customer and the most recent order for that customer is, say, 6/1/2019, then the X-Axis scales so that it ends on approx 6/1/2019.  I can set the end to 5/12/2020, but then this would have to be done periodically as the year progresses.

Am I supposed to do something with the measure other than using it on the filter pane?

Thanks again,

Jeff