X-axis with day and month

[HG_16159]

Hi, 

I've been browsing posts but can't seem to find a solution that actually works with my data. I'm a bit of a newbie still, but am enjoying learning a new way to present data that isn't R. 

Below I have two graphs. The bottom one looks exactly the way I would like it, except the x-axis is based off DOY, and the top graph has the correct x-axis, but vis is all wrong. 

Things I've tried: 

- Drilling up a level

- Changing the date hierarchy / trip_start_date back and forth between continuous & categorical. 

- Making all sorts of date columns in different formats to use as labels: 

I'm sure there is an easy solution. I appreciate your kind responses.

Data: 

trip_seq_no	trip_start_date	Year	DOY	effort_seq_no	location_id	SI_density
3218915	43676	2019	211	5440135	LLS-2	1.525062
3218915	43676	2019	211	5440136	LLS-2	0
3218915	43676	2019	211	5440137	LLS-2	0
3218915	43676	2019	211	5440138	LLS-3	0
3218915	43676	2019	211	5440139	LLS-3	0
3218915	43676	2019	211	5440140	LLS-3	1.715833
3218917	43693	2019	228	5440159	LLS-2	0
3218917	43693	2019	228	5440160	LLS-2	0
3218917	43693	2019	228	5440161	LLS-2	2.005025
3218917	43693	2019	228	5440162	LLS-3	1.873758
3218917	43693	2019	228	5440163	LLS-3	1.864666
3218917	43693	2019	228	5440164	LLS-3	0
3218919	43718	2019	253	5440183	LLS-2	0
3218919	43718	2019	253	5440184	LLS-2	0
3218919	43718	2019	253	5440185	LLS-2	11.52958
3218919	43718	2019	253	5440186	LLS-3	11.45957
3218919	43718	2019	253	5440187	LLS-3	3.397393
3218919	43718	2019	253	5440188	LLS-3	0
3218943	44007	2020	177	5440231	LLS-2	5.385557
3218943	44007	2020	177	5440232	LLS-2	5.290581
3218943	44007	2020	177	5440233	LLS-2	8.109403
3218943	44007	2020	177	5440240	LLS-3	3.306965
3218943	44007	2020	177	5440241	LLS-3	0
3218943	44007	2020	177	5440242	LLS-3	14.44517
3218946	44028	2020	198	5440267	LLS-2	10.12216
3218946	44028	2020	198	5440268	LLS-2	0
3218946	44028	2020	198	5440269	LLS-2	0
3218946	44028	2020	198	5440276	LLS-3	3.137031
3218946	44028	2020	198	5440277	LLS-3	1.584843
3218946	44028	2020	198	5440278	LLS-3	0
3218948	44041	2020	211	5440291	LLS-2	0
3218948	44041	2020	211	5440292	LLS-2	0
3218948	44041	2020	211	5440293	LLS-2	0
3218948	44041	2020	211	5440300	LLS-3	0
3218948	44041	2020	211	5440301	LLS-3	2.077579
3218948	44041	2020	211	5440302	LLS-3	0
3218954	44093	2020	263	5440357	LLS-2	1.946817
3218954	44093	2020	263	5440358	LLS-2	1.995736
3218954	44093	2020	263	5440359	LLS-2	0
3218954	44093	2020	263	5440360	LLS-3	0
3218954	44093	2020	263	5440361	LLS-3	0
3218954	44093	2020	263	5440362	LLS-3	0

[HG_16159 · Accepted solution]

We solved this issue by converting our graph to a Denebe object, with the code below. While I lost the slider functionality, I retained the bulk of the functionality in the graph and can continue to filter the data based off location & year. It's frustrating that it was seemingly impossible to accurately display interannual seasonal fluctuations on the same graph with a simplified x-axis using the Line Chart option. 

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {"name": "dataset"},
  "mark": "line",
  //"mark": "area",
  "encoding": {
    "x": {
      "field": "DOY",
      "type": "quantitative",
      "axis": {
        "values": [1, 32, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335],
        "labelExpr": "timeFormat(toDate(datum.value * 86400000), '%b')",
        "title": "Month"
      }
    },
    "y": {
      "field": "Average of SI_density",
      "type": "quantitative",
      "axis": {
        "title": "Mean density (1000m-3)"
      }
    },
    "color": {
      "field": "Year",
      "type": "nominal",
      "title": "Year"
    },
    "tooltip": [
      {"field": "Year", "type": "nominal", "title": "Year"},
      {"field": "Formatted_Month", "type": "quantitative", "title": "Date"},
      {"field": "Average of SI_density", "type": "quantitative", "title": "Count"}
    ]
  }
}

[v-veshwara-msft]

Hi @HG_16159 ,

Just checking in again as we haven’t heard back from you. If the provided solutions addressed your issue, kindly mark the helpful reply as the Accepted Solution to assist others with similar queries.

If your issue is still unresolved or you need further help, feel free to reach out.
Thank you.

[v-veshwara-msft]

Hi @HG_16159 ,

Following up again to check if your query has been resolved. If so, please consider marking the helpful response as the Accepted Solution to assist others facing similar issues. If you still need further assistance, please reach out.

Thank you!

[v-veshwara-msft]

Hi @HG_16159 ,

Thank you for being a part of the Microsoft Fabric Community.

Thanks, @lbendlin , for your valuable response.

Just following up to check if your query has been resolved. If so, please consider marking the helpful response as the Accepted Solution to assist others facing similar issues. If you still need further assistance, please reach out.

Thank you!

[HG_16159]

Thank you for your response and working on this. Since the 'trick' is to put the same year for all years on the x-axis label, there's no way to display it full extended without having the year displayed as well? That seems like an odd flaw for the program to have. I noticed in the file that there is no date hierarchy for the new column to help "force" the x-axis labels. Is this because there are not unique values throughout the column? 

[lbendlin]

I noticed in the file that there is no date hierarchy for the new column to help "force" the x-axis labels. 

Date hierarchies are generally useless (personal biased opinionated statement).  The Line chart visual is automatically rendering the x axis labels according to the available space and the data range your user looks at if that is fed by a date value.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[HG_16159]

@lbendlin Apologies, data has been added. 

[lbendlin]

Your sample data is not very representative but here is a first stab at how to handle this.

The dynamic X axis unfortunately also means that you cannot influence the labels, so fully extended the zoom sliders will expose the trickery.