Forum Discussion
Dates: change Start & End on axis
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 :)
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.
7 Replies
- konstantinos
Memorable Member
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...
- Greg_Deckler
Community Champion
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.
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")
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
MP_123Microsoft Employee
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
Helper II
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.
singliarThe 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
