How can I show months or years of data and filter by date?

[ChrisNelsonPE]

If I have a table like:

CREATE TABLE [dbo].[sales](
	[Buyer] [nchar](10) NULL,
	[PurchaseDate] [date] NULL,
	[Amount] [money] NULL
) ON [PRIMARY]

And I want to chart total sales by day for more than a month.  Or more than a year.  If I use a date hierarchy for PurchaseDate, I picking Day shows only 31 bars with, I think, the sales for the first of every month on 1, for the second on 2, etc.  If I pick quarter, I believe I see Q1 of every year on the first bar.

I saw one suggestion to create a new column like Axis_Date = FORMAT(PurchaseDate, "mmm dd") I have two problems.  Not only do June 1, 2019 and June 1, 2020 show up on the same bar but they sort after July.

I looked for a way to format the axis label but that didn't seem to help.  And even if I could format it like yyyy-mm-dd, that's not always the user's preferred format.

I believe I'm missing something fundamental but I don't see what it is.

[ChrisMendoza]

@ChrisNelsonPE - take a look at https://www.sqlbi.com/articles/creating-a-simpler-and-chart-friendly-date-table-in-power-bi/ . I think you'll find a solution within the pattern.

[ChrisMendoza]

@ChrisNelsonPE - Have you tried using a 'Date' table?

[ChrisNelsonPE]

@ChrisMendoza , I'm so new to Power BI I don't even know what that is. 😕

[V-lianl-msft]

Hi @ChrisNelsonPE ,

Based on your description, I guess you are using direct query connection mode.

For sorting problem, please refer to this video. For "MM-DD" of different years, please use the year as the legend of the graph.

https://www.youtube.com/watch?v=n_gnuFS8qoY&feature=youtu.be 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChrisNelsonPE]

No, I'm importing my data.  But if that video is still relevant, I'll take a look.

[V-lianl-msft]

Hi @ChrisNelsonPE ,

For sorting month names in import mode, please refer to this:

https://databear.com/power-bi-tips-sort-by-month-name/ 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.