Formatting x-axis date from Year to Month-year

[Anonymous]

I have data from June 2021-Jan 2022. The x-axis contains the dates and y-axis contains the value on that date. Power BI is showing just two values on the x-axis, 2021/2022. I would like to have it June 2021, July 2021... etc. Anyone know how to achieve this? Picture is shown below.

Here is also a snippet of my data.

Does anyone know why the value on the y-axis is then going to like 0.5 billion when it should be around 3000 max?

[visheshjain]

Hi @Anonymous ,

I think I agree with @amitchandak that you need the MTD measure for the graph that you are trying to achieve.

You can share your file from google or one drive by inserting the link or you try to attach the file by clicking on insert media.

Thank you,

Vishesh Jain

[visheshjain]

Hi @Anonymous ,

I do not know your data completely, but from what I can see is that the Y-axis is summing up all the values since you have only a single date.

You will need a calendar table and probably another table for time, if you plan on using the time field.

Else you can convert the data type of the column into a date and remove duplicates so that you have only 1 entry per date.

This is more of a modeling problem, the solution to which will depend on what you are trying to visualize through your data.

Thank you,

Vishesh Jain

[Anonymous]

Hi @visheshjain 

See my response to amit. I am not sure what to do to get the correct value for the value column.

[amitchandak]

@Anonymous , Create a date column without time

DAX

Date = [datetime].date
or
Date = date(year([datetime]),month([datetime]),day([datetime]))

In power query

DateTime.Date([datetime])

Join with date table

with columns

Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")

Sort Month year on Month year sort

Join date of table with date of date and plot data using month year of date table

[Anonymous]

@amitchandak 

Thanks. I tried this and got this.

The shape I am expecting is shown below.

Do you know why I am not getting this? I am guessing it's because of the sum of values option selected but not sure what I need to change it to.

I just want power bi to plot the value at each date, like in excel.

[visheshjain]

Hi @Anonymous ,

In the values field, select 'don't summarize' and I think this should work, as it will just plot the points.

If this does not work, then please can you share a sample data and I'll try to work on it.

Thank you,

Vishesh Jain

[Anonymous]

@visheshjain 

I have already selected don't summarize but when I drag it into the Values field for the line graph, it automatically selects sum and makes me select at least one of the options such as sum, count etc. I don't seem to have an option to attach files? Not sure if my account has permission for it. Pictures below show what I am saying though.

[amitchandak]

@Anonymous , I think you need to use datesmtd

a measure like

CALCULATE(SUM(Table[value]),DATESMTD('Date'[Date]))

use date from date table on axis

[Anonymous]

@amitchandak 

Sorry, not sure I follow. I have all my data in just one table as below. Ignore the third column.

The last 3 columns were added as you wrote above and I used the month year column as my axis and Value for value. Using date as my axis would just give 2021/2022 in the line graph. See my response to @visheshjain to see what the problem was. 

[amitchandak]

@Anonymous , the shape you wanted is only possible when you use date on axis. That too from the date table. Joined with date and created MTD measure as shared above

[Anonymous · Accepted solution]

I managed to do it. All I needed to do was change from date hierarchy to DateTime in the axis field.