In this challenge, a company’s fiscal year runs from October 1st through September 31st. How does the company perform in the fiscal year? Let’s see.

The data source located here. It contains financial ‘transactions’ from Oct 2018 to May 2020.

Check the whole requirements here: https://www.workout-wednesday.com/2020w26/

Step #1: Setting up the Fiscal Years

• Determine the months using the Period field.

• Make a new DATE then set the Fiscal Year Start from October. Right-clicking on the DATE field and selecting Default Properties -> Fiscal Year Start -> October.

Now we have the right Fiscal Date.

Let’s calculate the Current Values.

Step #2: Current Fiscal YTD

This calculation returns values from Oct 2019 to May 2020.

Step #3: Prior Fiscal YTD

This calculation returns values from Oct 2018 to May 2019.

Step #4: Compare Parameter

I created a string parameter with ‘FYTD vs Prior FYTD’ and ‘FYTD vs Budget FYTD’ records. Of course, if you want to take care of the performance of the report you need to make an integer parameter instead of a string.

Step #5: Current vs. Prior/Budget Value

Now we can create the remaining required fields that we’ll use to represent the current and prior values based on the parameter selection.

In the requirement of the challenge I determined:

• If the Account Number starting with a 5 that will be SALES value.
• If the Account Number starting with a 5 or 696 that will be OPP value.

You can define these requirements in a calculation or in the filter panel.

I used the filter option in my workbook.

• Current Value

Before we make the Current FYTD vs. Prior/Budget FYTD comparison calculation, we need to create Budget parameters for Sales and OPP (M).

Now we are ready to define the Current Value based on the Comparison Parameter selection.

*Note: because I determine the Sales and OPP Value using a quick filter on the relevant worksheets (Account Number starting..) this FY Dynamic Value (Current) calc can be used for both Sales and OPP current values.

Prior Value

For prior values, ​​it is necessary to make separate calculations because we want to set a separate budget for Sales and Prior.

• Dynamic Sales (Prior)

• Dynamic OPP (Prior)

*Note: Because the  Budget parameters aren’t actually in millions, I multiplied them and translated them to make the comparisons easily.

Step #6: Compute the differences

• Diff Sales

• Difference Sales %

• Diff OPP

• Difference OPP %

• Color Sales

• Color OPP

Finally, we calculated all the fields we need to build the dashboard.

Step #7: Building the visualizations

SALES 

Bar chart

• FY Dynamic Value (Current) on Columns
• Dynamic Sales (Prior)on Detail
• Colour Sales on Colour
• Add Dynamic Sales (Prior)as a reference line, show its label
• Show the label of the bar chart and align it to the middle right

KPI

• Diff Sales on Text
• Difference Sales % on Text
• Set the custom format of the Diff Sales to display as $ in millions and add arrow to show positive or negative: ▲”$”#,##0,,M;▼”$”#,##0,,M

Trend Line

• Discrete MONTH(DATE) on Columns
• Value on Rows
• YEAR(DATE) on Colours
• Add the Average Value to Reference Line
• Show the label of the recent month on the line and highlight it with a larger circle

We need another calculated field for this. I promise this will be the last calculation.

• Last Month

{MAX(([DATE]))}

• Last Point

IF [Max Month]=[DATE] THEN [Value] END

• Add the Last Point field to Columns, make dual-axis – don’t forget to synchronize axis. Adjust the Size to suit.

Please make the same process to build the charts of OPP.

Step #8: Custom Legends

Year Legend

Let’s make a different legend than the standard square. The custom circle legend is created as below:

KPI Diff Legend

• I created 3 dummy measures for the 3 categories.
• MIN(1) – Difference > 5%
• MIN(0) – 5% = < Difference <= 5 %
• MIN(2) – Difference < – 5%
• Rename the aliases of the Measure Names
• Set the right colors using Measure Names on Color

I made the sheet as below