How to compare past two years data on a clustered column chart by fiscal Year- monthly basis?

[Rck7]

Hi Friends,

I have a table with a date column with dates from (27Oct-14 - 26Oct17). I would like to build a clustered column chart with data bars showing the total number of tokens on a monthly basis for the Fiscal Year's (Year1: 27/Oct/14-26/Oct/15,  Year2: 27/Oct/15- 26/Oct/16, Year3: 27/Oct/16- 26/Oct/17) side by side. 

How would I able to create  measures and use them/ if there is any other way to achieve the result?

Glimpse of my data: 


 
Any help would be appreciated!!!!!!!!!
Thank you.

[chethan]

@Rck7 refer to the Link

https://community.powerbi.com/t5/Desktop/Fiscal-Year-and-Month-Order/m-p/68044

[v-yulgu-msft]

Hi @Rck7,

In source table, create two calculated columns:

Year No = Test1[Date].[Year]

FY =
IF (
    IF (
        Test1[Date].[MonthNo] <= 10
            && Test1[Date].[Day] < 27,
        Test1[Year No] - 1,
        Test1[Year No]
    )
        = 2014,
    "Year1",
    IF (
        IF (
            Test1[Date].[MonthNo] <= 10
                && Test1[Date].[Day] < 27,
            Test1[Year No] - 1,
            Test1[Year No]
        )
            = 2015,
        "Year2",
        "Year3"
    )
)

Add corresponding fields into clustered column chart as shown in below screenshot.

Best regards,

Yuliana Gu

[Rck7]

@v-yulgu-msft Thank you for your solution. Would you mind explaining/commenting your measure? Does all the measure you have suggested goes in to a one single new column?  I am trying to understand  your measure!! 

Thank you.

[v-yulgu-msft]

Hi @Rck7,

First, I get the year number from date column using 

Year No = Test1[Date].[Year]

Then, based on this syntax, I created a new column showing fiscal year number.

2014/1/1~2014/10/26 -> Fiscal Year 2014 -> Year1

2014/10/27~2015/10/26 -> Fiscal Year2015 -> Year2

2015/10/27~2016/10/27 -> Fiscal Year2016 -> Year3

In short, in a year, the day before 10/26 is calculated into previous fiscal year, the day after 10/27 is considered as current fiscal year. 

Best regards,
Yuliana Gu

[Rck7]

@v-yulgu-msft, Thank you for explaining. I have tried your method of solution and I am able to see the comparision  chart for the years 1,2,3,4.  But, I am facing a problem where the months on x-axis are starting from January-December whereas it should start from october- current month (as the fiscal years starts from Oct to Oct). 

My chart: 




How would I be able to see the months on x-axis to start from oct- oct ?

Kindly, help!
Thank you.

[v-yulgu-msft]

Hi @Rck7,

After you have achieved the result in above post, in Query Editor mode, you need to create some extra columns.

Please see stpes as shown in below screenshots.

Return back to data view, sort the Month column based on [SortCategory].

In chart visual, rather than add date hierarchy into X-axis, please add [Month Number] to X-axis. In that case, you can sort the X-axis to starts from Oct to Oct.

Regards,
Yuliana Gu

[Rck7]

@v-yulgu-msft.Thanks for the reply. I have tried what you have suggested but not sure if it is ordered in correct format or not. Addititionally, I want to see the month names instead of the numbers.

I want to build my chart where I could see yearly value bars for (FY1, FY2,FY3)& FY4(whichis going to start from
(27/Oct/17- 26/Oct/18) allingned on x-axis with the months (with names)starting from October to October(which are the fiscal year contract months). 


Kindly, help me with this problem.
Thank you.

[v-yulgu-msft · Accepted solution]

Hi @Rck7,

I cannot locate to the Query Editor mode in your pbix file as I don't have the valid credential.

To show Month name on X-axis is the same. You only need to make some adjustment, please see below:

=Date.MonthName([Date])

All the other steps are the same.

Best regards,
Yuliana Gu

[kgamman]

@v-yulgu-msft 

I am very grateful for your instructions on this as it has got me so much closer to my goal on one particular report, but I am experiencing the opposite problem to Rck7--I cannot get my months to display in the right calendar order.

I need to be able to compare 2019 to 2020 in side by side collumns, and I followed your instructions to create a custom collumn and a Conditional collum, and I can get 2019 and 2020 to compare this way, however, it seems to be placing the collumns in alphabetical order.  I cannot adjust the date hierarchy because my Date/time opened is in the Legend position and only gives me the option for Year.  Month number occupies the Axis but there is no option to sort by the month number, or at least, not that I can see.  Thoughts?

Any advice you could give me would be greatly appreciated.

[Rck7]

@v-yulgu-msft. Thank you very much for the help. It worked!!!!!!!!!