Forum Discussion
Stacked Area Chart - Convert to Monthly
Hi,
I have some daily data which I have converted into a stacked area chart. The chart looks messy as there are too many data points so looking to convert to monthly data for the chart.
However, I can't seem to work out how to do this. The data is in a date format ('Start Time') so should be easy enough but not sure where I would find this.
Thanks for help in advance.
Oh, I missed to tell you to add the Month column as well. You can add it as a new column in your date table with a formula like this:
Month_Name = FORMAT(Table[Date], "MMMM")
3 Replies
You need to have a calendar/date table.
First, you need a date column in that table. You can extract it through Power Query by selecting the time column, going to Add Column and selecting the date option:
Then, you can close & apply and create a new table with DAX (go to Table view to find the option easily) and use the command CALENDARAUTO() to generate an automatic calendar table.
After that, create a relationship between the date column from the new table and the date column (recently added) from your current table.
Finally, on your axis use the month column from the new table as the field.
Hi - thanks for your help.
Just had a couple of follow up questions:
- When adding the date column to the existing data should this be 'Date Only'?
I followed the steps and I'm able to create the relationship however I dont have a month column to choose from? If I add a Month column, rather than 'Date Only', I only get months 1 - 12 however I need to be able to have a distinction between Month 1 2016 vs Month 1 2017/18/19 etc.
I have the below option which I added to Axis for my visual, however this is still daily data
Oh, I missed to tell you to add the Month column as well. You can add it as a new column in your date table with a formula like this:
Month_Name = FORMAT(Table[Date], "MMMM")
