cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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?

4 REPLIES 4
Microsoft Employee

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

Helper I

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

Community Champion

@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

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

Proud to be a Datanaut!
Community Champion

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/

Proud to be a Super User!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors