FORMAT Month Issue - A Month isn't a month

[Anonymous]

I use the following DAX function to create a value called Month in my date table:

Month = FORMAT('Date'[Date],"Mmmm")

I get back fully spelled-out month names, with the first letter capitalized, which is what I want.  Unfortunately, I am trying to compare a value over several years, within the same month, and I am getting this:

Why wouldn't I have one December here, that includes both December 2018 and December 2019, for purposes of comparision?  My Data type for the Month column is Text, which I half-suspect could be my problem, but I don't know what else would work for it, since the name of a month is fundamentally a text string.  Thanks in advance for your help.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Based on your data, you can see there's the null value in FY19 and FY20. I'd suggest you create a new table with the column 

Table 2 = VALUES('Table'[MonthColumn])

Then manage the relationship between table 1, then use the column in table2 as X-axis.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Based on your data, you can see there's the null value in FY19 and FY20. I'd suggest you create a new table with the column 

Table 2 = VALUES('Table'[MonthColumn])

Then manage the relationship between table 1, then use the column in table2 as X-axis.

[Anonymous · Accepted solution]

This worked.  Thanks for helping!

[amitchandak]

@Anonymous , you have month on-axis and year as a legend.  and use a simple measure. that should work

[Anonymous]

Thanks for your response, but I don't follow you.  I have Fiscal Year as the legend in the line chart, not month.  Likewise, in the Matrix, I have Fiscal Year in the column.  Does that change your advice about the Measure?  And if so, what would the proposed measure do?