Fiscal Year to Year Change as a Percentage

[intern56]

Hello I am trying to find Year to Year change in sales data as a percentage and I am not quite sure how to go about the solution. One of the issues is that our Fiscal year starts on April 1st (ends March 31st) and so I do not know how to capture that. Additionally we do not have sales every day so I do not think I can use ParralelPeriod or SamePeriodLastYear. An example of what I am looking for is below:

                                  # of Orders                                                                                                   Percent Change in Orders

                      FY1   |     FY2      |    FY 3                                                                                           FY1    |     FY2      |    FY 3

Region 1:         1            2                 1                                   Becomes                    Region 1:        --           100%        -50%

Region 2:         2            3                 1                                                                     Region 2:         --           150%        -33%

I have a table called Data. Data has the Order Date, Order Quantity, and Fiscal Year (ex: 10B is 2010, 11B is 2011).

I also created a Calendar table as other discussions suggested but I am not entirely sure how to use it to my advantage (or if I can use it).

Any help would be great.                                               

[intern56 · Accepted solution]

Hello,

Unforutnately I can not share some of my data. I realize that I made a mistake in Region 2 as it should be a 150% and then -66% (not -33%). 

I did however fix my solution by using my calendar table which has every date between my min and max date.

[v-yuezhe-msft]

@intern56,

Could you please share sample data of your Data table and post expected result based on the Data table?

In your sample, you mention Region field, does the field exist in your data table? And how do you calculate percent change for Region 2(150%, -33%)?


Regards,

[intern56 · Accepted solution]

Hello,

Unforutnately I can not share some of my data. I realize that I made a mistake in Region 2 as it should be a 150% and then -66% (not -33%). 

I did however fix my solution by using my calendar table which has every date between my min and max date.

[v-yuezhe-msft · Accepted solution]

@intern56,

Glad to hear that the issue is solved, you can accept your reply as answer, that way, other community members would easily find the answer when they get same issues.

Regards,