Dates: change Start & End on axis

[jbowler]

Can someone help explain how to use dates with the Start & End? Scoured help files and community but cannot find a mention

I have a date field in my x axis (continous) visualisation. i want to specifiy the start and end month for charts. mainly so that they look uniform when comparing, irrespective if some months have null values.

I don't want to switch to categorical (due to the null values for some months)

When trying to pass values to the Start & End boxes it appears that only numbers are legal characters. My dates start from 01/06/2014. I had to pass a number of 11111111111111 to begin to see the scale shifting. what is happening here?

thanks All 🙂

[singliar]

The behavior is consistent with the axis being plotted on a scale of milliseconds from midnight of 1/1/1970.

So to plot a time range from Nov 1, 2014 to May 1st, 2015, I did this little table in Excel and used the last column as range for the time axis:

Date	Num.Date	Baseline	Days since	Seconds since	ms since
10/31/2014	41943	1/1/1970	16,374	1,414,713,600	1,414,713,600,000
5/1/2015	42125	1/1/1970	16,556	1,430,438,400	1,430,438,400,000

Tomas

[Greg_Deckler · Accepted solution]

You could create a calculated field like =FORMAT([date],"mm") or =FORMAT([data],"mmyyyy". Add this as a filter to your visualization and select the same items for each visualization and now your x-axis should be uniform across your visualizations because you are filtering to the same months or month/years.

[MP_123]

@Greg_Deckler@konstantinos

is it possible somehow to change the start - end without creating new columns?(live connection)

my x axis is monthes over year and it will grow and grow every month

thanks!!

[curtisp]

I wanted to show just records in report chart for past 30 days..  

So I created Custom Column:

if Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow()) - [date])) < 31 then "30days" else ">30days"

I used this Custom Column as filter in report just to show "30days". This flag updates dynamically with each data refresh so report chart always shows past 30 days data.

[jbowler]

Sorry - just checking this again...

When using the formated column: Month = FORMAT('KPI_Account'[MonthNoFormat],"MMM YY")

it removes the choice to select continuous or categorical. And it appears to switch the date to categorical and NOT keep the date in the correct order. (E.g. displaying Jun 14 after Jan 15...)

For now, I'll use my existing numerical date and just pass it as a categorical. At least this date appears to recognise the correct date order

Thanks all

[jbowler]

Thanks very much

This method enables a month label to display for each month that has a value - something I couldn't control prior. That is important and makes the visualisaiton by itself readable.

It doesn't ensure a uniform axis - null values are still simply omitted from the chart.

But thinking further a uniform axis is not really the right solution anyway, as in theory I'd need to show all 15 date points (months) to ensure charts are uniform.

FormatDate = FORMAT('Table2'[Month],"MMM YY")

[konstantinos]

Interesting..You can do it with DAX but will be complicated..I tried to  convert the date to number in excel and pass this value in Start & End boxes..When I add the Start date it moves but when add End it crashes the chart - the x-axis disappears...You can try it though...