Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@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.
Proud to be a Super User!
@ChrisNelsonPE - Have you tried using a 'Date' table?
Proud to be a Super User!
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.
No, I'm importing my data. But if that video is still relevant, I'll take a look.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |