How to Get QoQ and YoY Growth using Fiscal Quarter.

[subrakal]

Hi,

I have a data which Contains Columns of Region,Scenario, Business Type, FQ, FY, Revenue.

Basically these collumns will have a data like below

Region="APJ","AMS","EMEA"

Scenario = "Present","Future"

Business Type ="Distributor","Reseller"

FQ = "Q16"," Q216","Q316","Q416"...."Q417"

FY=  "FY 14", "FY15", "FY 16", "FY 17"

Revenue= Numerical Data

Here Business Type and Region will be in a filter and I have to create a Table and Chart in Power BI as below. I don't have any challenge of create a below table except QoQ and YoY and also Please note FQ and FY is  string and we don't have any date column in the rawdata. we have only FQ and FY which I highlighted above. Can anyone please help me as this is quit urgent and I have to give demo to my senior management.

Thanks and Regards

Kalidas Subramaniam

[v-jiascu-msft]

Hi @subrakal,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft]

@subrakal,

Hi Kalidas,

Could you please post a sample with dummy data in text mode? It's hard to create formula with the current information. Maybe you could try to add an index. Then the formula could look like this: Calculate([...], filter('table', 'table'[index]<=min('table'[index]))).

Best Regards!

Dale

[subrakal]

Hi Dale,

Thanks for your reply. I have attached a table below for your ref.  Sorry but I an not clear with your solution regarding my requirement. Can you please explain me briefly how this can be acheived in Power BI.

Region                         Scenario    Q116 Q216 H116  Q316  Q416  H216  FY16  Q117  Q217 H117 Q317 Q417 H217 FY17
Distributor APJ             Present      100     85      185     98       99     197    382      67      67    134     54       54    108   242
                          QoQ                                85%             115%  101%                      68%  100%            81%   100%
                          YoY                                                                                                                                                         63%

YoY YoY Formula=Current year/Previous Year


QoQ QoQ Formula =Current Quarter/Previous Quarter

Example FY17 Q1 QoQ=Q117/Q416

Regards 

Kalidas S

[v-jiascu-msft]

@subrakal,

Hi Kalidas,

Could you please post your source data the table you already have now? Maybe it looks like this:

Region	Scenario     Business Type	FQ	FY	Revenue
APJ	Present      Distributor	Q16	FY14	1000000
AMS	Future	     Distributor	Q216	FY14	2000000

Best Regards! 

Dale

[subrakal]

Hi Dale,

Please find attached source data as requested. Sorry I am not able to attach the excel file. But you can copy the entire Data to excel and make it Text to Column with Space delimiter. it will come in the format you required. I tested it.

Region Scenario Business Type FQ FY Revenue
CEMA Present Reseller Q114 FY 14 123.12
CEMA Present Reseller Q214 FY 14 137.22
CEMA Present Reseller Q314 FY 14 151.32
CEMA Present Reseller Q414 FY 14 165.42
CEMA Present Reseller Q115 FY 15 179.52
CEMA Present Reseller Q215 FY 15 193.62
CEMA Present Reseller Q315 FY 15 207.72
CEMA Present Reseller Q415 FY 15 221.82
CEMA Present Reseller Q116 FY 16 235.92
CEMA Present Reseller Q216 FY 16 250.02
CEMA Present Reseller Q316 FY 16 264.12
CEMA Present Reseller Q416 FY 16 278.22
CEMA Present Reseller Q117 FY 17 292.32
CEMA Present Reseller Q217 FY 17 306.42
CEMA Present Reseller Q317 FY 17 320.52
CEMA Present Distributor Q114 FY 14 334.62
CEMA Present Distributor Q214 FY 14 348.72
CEMA Present Distributor Q314 FY 14 362.82
CEMA Present Distributor Q414 FY 14 376.92
CEMA Present Distributor Q115 FY 15 391.02
CEMA Present Distributor Q215 FY 15 405.12
CEMA Present Distributor Q315 FY 15 419.22
CEMA Present Distributor Q415 FY 15 433.32
CEMA Present Distributor Q116 FY 16 447.42
CEMA Present Distributor Q216 FY 16 461.52
CEMA Present Distributor Q316 FY 16 475.62
CEMA Present Distributor Q416 FY 16 489.72
CEMA Present Distributor Q117 FY 17 503.82
CEMA Present Distributor Q217 FY 17 517.92
CEMA Present Distributor Q317 FY 17 532.02
CEMA Future Reseller Q415 FY 15 546.12
CEMA Future Reseller Q116 FY 16 560.22
CEMA Future Reseller Q216 FY 16 574.32
CEMA Future Reseller Q316 FY 16 588.42
CEMA Future Reseller Q416 FY 16 602.52
CEMA Future Reseller Q117 FY 17 616.62
CEMA Future Reseller Q217 FY 17 630.72
CEMA Future Reseller Q317 FY 17 644.82
CEMA Future Reseller Q417 FY 17 658.92
CEMA Future Distributor Q317 FY 17 673.02
CEMA Future Distributor Q417 FY 17 687.12
CEMA Quota Reseller Q115 FY 15 701.22
CEMA Quota Reseller Q215 FY 15 715.32
CEMA Quota Reseller Q315 FY 15 729.42
CEMA Quota Reseller Q415 FY 15 743.52
CEMA Quota Reseller Q116 FY 16 757.62
CEMA Quota Reseller Q216 FY 16 771.72
CEMA Quota Reseller Q316 FY 16 785.82
CEMA Quota Reseller Q416 FY 16 799.92
CEMA Quota Reseller Q117 FY 17 814.02
CEMA Quota Reseller Q217 FY 17 828.12
CEMA Quota Reseller Q317 FY 17 842.22
CEMA Quota Reseller Q417 FY 17 856.32
CEMA Quota Distributor Q115 FY 15 870.42
CEMA Quota Distributor Q215 FY 15 884.52
CEMA Quota Distributor Q315 FY 15 898.62
CEMA Quota Distributor Q415 FY 15 912.72
CEMA Quota Distributor Q116 FY 16 926.82
CEMA Quota Distributor Q216 FY 16 940.92
CEMA Quota Distributor Q316 FY 16 955.02
CEMA Quota Distributor Q416 FY 16 969.12
CEMA Quota Distributor Q117 FY 17 983.22
CEMA Quota Distributor Q217 FY 17 997.32
CEMA Quota Distributor Q317 FY 17 1011.42
CEMA Quota Distributor Q417 FY 17 1025.52 

Regards

Kalidas 

[v-jiascu-msft]

@subrakal,

Hi Kalidas,

I created a report. It worked, though it wasn't the same with what you wanted. 

1. Create a new table called "FDates".

FDates =
DISTINCT (
    SELECTCOLUMNS (
        Table2,
        "fy", [FY],
        "fq", [FQ],
        "index", CONCATENATE ( MID ( [FQ], 3, 2 ), MID ( [FQ], 2, 1 ) )
    )
)

2. Establish relationship.

3. Create a measure.

QoQ/YoY =
VAR previousQ =
    CALCULATE (
        MAX ( 'FDates'[Index] ),
        FILTER ( ALL ( 'FDates' ), 'FDates'[index] < MIN ( 'FDates'[Index] ) )
    )
VAR previousY =
    CALCULATE (
        MAX ( 'FDates'[fy] ),
        FILTER ( ALL ( 'FDates' ), 'FDates'[FY] < MIN ( 'table2'[FY] ) )
    )
RETURN
    IF (
        ISFILTERED ( 'FDates'[fq] ),
        DIVIDE (
            SUM ( 'table2'[Revenue] ),
            CALCULATE (
                SUM ( table2[Revenue] ),
                FILTER ( ALL ( 'FDates' ), 'FDates'[Index] = previousQ )
            ),
            0
        ),
        DIVIDE (
            SUM ( 'table2'[Revenue] ),
            CALCULATE (
                SUM ( table2[Revenue] ),
                FILTER ( ALL ( 'FDates' ), 'FDates'[FY] = previousY )
            ),
            0
        )
    )

More details, please review the attachment: https://1drv.ms/u/s!ArTqPk2pu-BkfxURHskKu4H1AFo

Best Regards!

Dale

[subrakal]

Thanks Dale. I am very new to Power BI. I will implement your solution to my Dashboard and will post my response whether it is resolved or not. Currently I am out of office and will post my response on  8th August. Moreover It would be nice if you have entered some comments on your script which will help me to understand easily. But Otherwise when I see the dashboard you attached here in the link shows good. 

Thanks a lot.

Regards

Kalidas S

[subrakal]

Hi Dale,

I created the measures and new columns as you advised. But when I tried to establish relation ship with the source table it says "You Can not create a relationship between these two columns because one of the columns must have unique values". But the column fq in the table FDate has unique value. Can you please advise me why I am getting this error. 

Regards

Kalidas S

[v-jiascu-msft]

@subrakal

Hi Kalidas,

I think there must be some blank values in the column "fq". You can find them out by sorting the column "fq". If you didn't forget the function "distinct", you have to check your data source.

FDates =
DISTINCT (
    SELECTCOLUMNS (
        Table2,
        "fy", [FY],
        "fq", [FQ],
        "index", CONCATENATE ( MID ( [FQ], 3, 2 ), MID ( [FQ], 2, 1 ) )
    )
)

Best Regards!

Dale