01-23-2017 03:41 PM
I have a line chart in which I am showing a 30 day window and I have created a text column called Month Abbreviation Day in my date table that looks like Jan 1, Jan 2 ...
I have a data set that won't have data on every day but I want the line to be continous. It appears continous on works with dates. How to I make my column a date column and have it in the format of "MMM d" ?
01-25-2017 01:05 AM
What do you mean continuous line? Do you want to display data points on each day in the line chart even though there is no data record on some specific day?
If that is the case, you need to create a Calendar table to show continuous date on X-axis. Below is my test result.
Create a calendar table.
DimDate = CALENDAR(DATE(2017,1,1),DATE(2017,1,31))
Insert a calculated column in calendar table and lookup the values which are added into Y-axis from dataset table.
DimSales = IF ( ( LOOKUPVALUE ( ContinuousLineChart[Sales], ContinuousLineChart[Date], DimDate[Date] ) ) <> BLANK (), LOOKUPVALUE ( ContinuousLineChart[Sales], ContinuousLineChart[Date], DimDate[Date] ), 0 )
Then, create line chart as below.
By the way, you said you created a text column called Month Abbreviation Day in date table and now you want to convert this column to date column in format of "MMM d", right? You can directly create a calendar table which contains continous date column and the date displays in "MMM d" format on axis by default, as is show in the image above.
If I have something misunderstood, please correct me and share the screenshots of your report design and expected output. And please provide some sample data so that I can reproduce your scenario.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
01-25-2017 05:04 AM
v-yulgu-msft, Thanks for the quick respone!
I have the data (Calendar) table established but what I am missing is how to format the date column with a custom format like "MMM d" for the X-Axis. The format drop down doesn't have this as an option and I am not sure where I can change the format string? Can you point me in the right direction for this?
Also my observation was that if I saved a calculated column in text format, that the Line Graph would only support this as categorical type for the X-Axis. Taking a some time for me to understand the differences in Power BI charting as compared to Excel.
Thanks for your help!