Fiscal Years in a Bar Chart

[P0W3RB1]

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?

[v-sihou-msft]

@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]

@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]

@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

[kcantor]

Try this:

http://powerpivotfaq.com/Lists/TGPPF/DispForm.aspx?ID=125

Here is the longer version:

http://www.powerpivotblog.nl/using-time-intelligent-powerpivot-functions-with-fiscal-year/

This should solve your problem.