Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous 

 

 

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!

View solution in original post

4 REPLIES 4
CheenuSing
Community Champion
Community Champion

Hi @Anonymous ,

 

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!
Anonymous
Not applicable

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

Hi @Anonymous 

 

 

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!

Hi @Anonymous 

 

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.