Forum Discussion

P0W3RB1's avatar
P0W3RB1
Icon for Helper I rankHelper I
10 years ago

Fiscal Years in a Bar Chart

I Have a bar chart, and a timline from the custom visuals where you can set a fiscal year which is great, but lets say a fiscal year is july to july, i can select that fiscal year on the timeline but the chart still shows Jan, Feb, March etc rather than July > to july ?

 

How to change that?

 

 

Need Help

4 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

    P0W3RB1

     

    In this scenario, you can create one column to show the “Month Year” (Type Text) which sorted by another created column “Year Month” (Type Whole Number). Please refer to following steps.

     

    1. Create a column to format the full date.
      Month_Year =
FORMAT ( Table1[Date], "MMM YYYY" )
    2. Create a column to convert the year and month to a whole number.
      Year_Month =
YEAR ( Table1[Date] ) * 100
+ MONTH ( Table1[Date] )
    3. Select column of “Month_Year” and then click ‘Sort By Column of Year_Month’.
    4. Drag a Timeline (select Date for Time) and a Stacked bar chart (select Month_Year for Axis) as below.

    Regards,

    • P0W3RB1's avatar
      P0W3RB1
      Icon for Helper I rankHelper I
      10 years ago

      v-sihou-msft i think this may have worked! im just checking all the figures add up but its looking good thank you! i will mark as resolved when i know for sure.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    P0W3RB1

     

    Check following.

     

    1. Do you have a calendar or date table.

    2. Create columns for MonthName = FORMAT([FullDate], "MMM"), Fiscal Year, FiscalMonthNumber

    3. FiscalYear can be based on formula like If (Month([FullDate]) >= 7, Year([FullDate]), Year([FullDate]) - 1)

    4. Similarly FiscalMonthNumber can be based on a formula If (Month([FullDate]) >= 7, Month([FullDate]) -6 , Month([FullDate]) +6 ). This will generate the month numbers as 1 for July and 12 for June.

    5. For the MonthName set the sort by column ass FiscalMonthNumber.

    6. When you place MonthName in the bar chart it will always show Starting with July.

     

    If it works please accept this as a solution and also give kudos.

     

    Cheers

     

    CheenuSing