cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bestmbaman Member
Member

years, month and days on the same X-Axis

Hi,

my manager has a stacked column report which is showing sales since 2012 by year up to 2019, then in 2019 it changes to monthly, and in April 2019 it changes to sales per day. I wanted to create the same in power bi with stacked column chart but not showing month for previous years and days for previous months because I know how to group dates.

Please help!

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
CheenuSing Super Contributor
Super Contributor

Re: years, month and days on the same X-Axis

Hi @bestmbaman 

 

 

Business Rule

  1. To show the measure as cumulated at year level if date range does not belong to current year of data set.
  2. To show the measure as cumulated at month level if date range belongs to current year but not in the last month of data.
  3. To show the measure as cumulated at day level if date range belongs to current year and in the last month  of data.

 

Solution

  1. Data set used has data from July 1 2005 to July 31 2008.
  2. The last month of data is July 2008.
  3. The current year based on calendar months (Jan – Dec) is 2008.
  4. Created a calendar table using the min and max dates in the dataset in power query with several columns added.
  5. Created a column to identify current year using the script                                                "IsCurrentCalYear",                              [CalendarYear] + 1 )
  6.           each List.Max(InsertCalendarMonthName[CalendarYear]) -  
  7. IsCurrentCalFiscalYear = Table.AddColumn(CalMonthYearName ,

             This will set the value as 1 for all dates in 2008 and 2,3,4 for all dates in

          2007,2006,2005 respectively.

  1. Created a column called MonthSequenceNumber
  2. Created a column to identify the current month using the script                        "IsCurrentMonth", each                                                 [MonthSeqNumber] + 1)
  3.                                     List.Max(InsertMonthSeqNo[MonthSeqNumber]) –
  4. IsCurrentCalFiscalMonth = Table.AddColumn(InsertMonthSeqNo ,

           This will set the value as 1 for July 2008 and from 2 onwards for months

          Prior to July 2008.

 

  • The magic column X-Axis NameThe values will be

  1. Using the business rule stated above created the X-Axis Name. This column will be used in the barchart.
  1. dates in July 2008.
  2. Months Name with year for Jan-Jun2008
  3. Year number 2007,2006,2005 for dates in the respective years.

 

  1. Creating X-Axis Name is not enough, it needs to sorted to the date order but having only value for sorting value.
  2. This is achieved in the steps
  3. Grouped to RemovedColumns in the power query for the calendar table.

 

I have attached the pbix for your reference.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
4 REPLIES 4
CheenuSing Super Contributor
Super Contributor

Re: years, month and days on the same X-Axis

Hi @bestmbaman ,

 

Can you share the pbix / data on one drive / google drive and post the link here to formulate a solution.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
bestmbaman Member
Member

Re: years, month and days on the same X-Axis

Hello @CheenuSing 

As I mentioned it was a print out of a report in excel not PBI! don't tell me it's a weird request since it's made in excel and being used so many times.I believe there should be an easy way to do it in PBI

CheenuSing Super Contributor
Super Contributor

Re: years, month and days on the same X-Axis

Hi @bestmbaman 

 

Business Rule

  1. To show the measure as cumulated at year level if date range does not belong to current year of data set.
  2. To show the measure as cumulated at month level if date range belongs to current year but not in the last month of data.
  3. To show the measure as cumulated at day level if date range belongs to current year and in the last month  of data.

 

Solution

  1. Data set used has data from July 1 2005 to July 31 2008.
  2. The last month of data is July 2008.
  3. The current year based on calendar months (Jan – Dec) is 2008.
  4. Created a calendar table using the min and max dates in the dataset in power query with several columns added.
  5. Created a column to identify current year using the script                                                "IsCurrentCalYear",                              [CalendarYear] + 1 )
  6.           each List.Max(InsertCalendarMonthName[CalendarYear]) -  
  7. IsCurrentCalFiscalYear = Table.AddColumn(CalMonthYearName ,

             This will set the value as 1 for all dates in 2008 and 2,3,4 for all dates in

          2007,2006,2005 respectively.

  1. Created a column called MonthSequenceNumber
  2. Created a column to identify the current month using the script                        "IsCurrentMonth", each                                                 [MonthSeqNumber] + 1)
  3.                                     List.Max(InsertMonthSeqNo[MonthSeqNumber]) –
  4. IsCurrentCalFiscalMonth = Table.AddColumn(InsertMonthSeqNo ,

           This will set the value as 1 for July 2008 and from 2 onwards for months

          Prior to July 2008.

 

  • The magic column X-Axis NameThe values will be

  1. Using the business rule stated above created the X-Axis Name. This column will be used in the barchart.
  1. dates in July 2008.
  2. Months Name with year for Jan-Jun2008
  3. Year number 2007,2006,2005 for dates in the respective years.

 

  1. Creating X-Axis Name is not enough, it needs to sorted to the date order but having only value for sorting value.
  2. This is achieved in the steps
  3. Grouped to RemovedColumns in the power query for the calendar table.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
CheenuSing Super Contributor
Super Contributor

Re: years, month and days on the same X-Axis

Hi @bestmbaman 

 

 

Business Rule

  1. To show the measure as cumulated at year level if date range does not belong to current year of data set.
  2. To show the measure as cumulated at month level if date range belongs to current year but not in the last month of data.
  3. To show the measure as cumulated at day level if date range belongs to current year and in the last month  of data.

 

Solution

  1. Data set used has data from July 1 2005 to July 31 2008.
  2. The last month of data is July 2008.
  3. The current year based on calendar months (Jan – Dec) is 2008.
  4. Created a calendar table using the min and max dates in the dataset in power query with several columns added.
  5. Created a column to identify current year using the script                                                "IsCurrentCalYear",                              [CalendarYear] + 1 )
  6.           each List.Max(InsertCalendarMonthName[CalendarYear]) -  
  7. IsCurrentCalFiscalYear = Table.AddColumn(CalMonthYearName ,

             This will set the value as 1 for all dates in 2008 and 2,3,4 for all dates in

          2007,2006,2005 respectively.

  1. Created a column called MonthSequenceNumber
  2. Created a column to identify the current month using the script                        "IsCurrentMonth", each                                                 [MonthSeqNumber] + 1)
  3.                                     List.Max(InsertMonthSeqNo[MonthSeqNumber]) –
  4. IsCurrentCalFiscalMonth = Table.AddColumn(InsertMonthSeqNo ,

           This will set the value as 1 for July 2008 and from 2 onwards for months

          Prior to July 2008.

 

  • The magic column X-Axis NameThe values will be

  1. Using the business rule stated above created the X-Axis Name. This column will be used in the barchart.
  1. dates in July 2008.
  2. Months Name with year for Jan-Jun2008
  3. Year number 2007,2006,2005 for dates in the respective years.

 

  1. Creating X-Axis Name is not enough, it needs to sorted to the date order but having only value for sorting value.
  2. This is achieved in the steps
  3. Grouped to RemovedColumns in the power query for the calendar table.

 

I have attached the pbix for your reference.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 424 members 4,578 guests
Please welcome our newest community members: