Line graph should show monthly sums not daily sums

[croberts21]

I'm still pretty new to PBI. I have Free PBI Desktop v2.100.1182 (64 bit free version Dec 2021) on Windows 10.

I made a measure to format a date as YYYY-MM via the Format tab bar, so it will return a date, not via the FORMAT() function, which returns a string. My line graph is supposed to sum work hours of a certain type by this YYYY-MM field called "YearMonth" but the line graph looks like all the points are daily points. The line graph is supposed to be monthly for the past 4 years.

How do I avoid using the FORMAT() function for the YYYY-MM date, and still get the line graph to show points by month?

Here's what I get, along with the definition of my YearMonth field.

I have another measure on this table called "Year_Month" but it uses the FORMAT() function, which converts the date to a string, so I am unable to use trendlines on that graph. This is what the second graph looks like.

I would like the first graph to look like the second graph but use a date of YYYY-MM which is still a date value (not a string) so I can use trendlines.

Thank you!

[AntonioM]

If you're wanting to keep the column as a date, you've done the right thing using the format as you've done.

Now you need to change your year month column to only have one date for each month, such as using 

Year_Month = EOMONTH(EmpTime[dcstartdate])

or

Year_Month = 
DATE(
    Year( EmpTime[dcstartdate] ),
    Month( EmpTime[dcstartdate] ),
    31
)

Now every date in the month will show at one point.

[croberts21]

There are not 31 days in every month. Could the number 31 cause problems in PBI?

[AntonioM]

Yes, you're right, I meant to put 1 instead of 31.

EOMONTH will give you 28/30/31 depending on the month, but I've sometimes needed to use the start of the month for aligning with the axis labels (depending on the visual)

[Greg_Deckler · Accepted solution]

@croberts21 Perhaps try to create a column like:

Column = DATE(YEAR([Date]),MONTH([Date]),1)

Format that as YYYY-MM and use that in your axis.