Fiscal year and calandar year are different. Need to display YoY growth and QoQ growth

[Myurathan]

I am fairly new to DAX. I have following table in the data model. 

Quarters column is hard coded. Fiscal year end is September. I want to have a pivot table displays YoY growth and QoQ growth. Slicers and time line should be connected to it. How to proceed from here? 

Thanks in advance.

[v-yuezhe-msft · Accepted solution]

@Myurathan,

Firstly, create the following columns in your table.

Year = YEAR(Table2[Date Col])

Month = MONTH(Table2[Date Col])

Fiscal Year = IF(AND([Year] =2014,[Month]<=12),"15",
IF(AND([Year] =2015,[Month]<=9),"15",
IF(AND([Year] =2015,[Month]<=12),"16",        
IF(AND([Year] =2016,[Month]<=9),"16",
" "))))

QuarterYear = Table2[Fiscal Year] & "-" & Table2[Quarter]

Secondly, create the following measures in your table.

ThisYearSales = CALCULATE( 
    SUM( Table2[Sales(m)] ), 
    FILTER( 
        ALL( Table2[Fiscal Year] ) , 
        Table2[Fiscal Year] = MAX( Table2[Fiscal Year] )
    ) 
)

PreviousYearSale = CALCULATE( 
    SUM( Table2[Sales(m)] ), 
    FILTER( 
        ALL( Table2[Fiscal Year] ) , 
        Table2[Fiscal Year] = MAX( Table2[Fiscal Year] ) - 1
    ) 
)

YOY = [ThisYearSales]-[PreviousYearSale]

QoQ = 
VAR Current_Quarter =
    MIN (Table2[QuarterYear] )
VAR Quarter_Year =
    LEFT (Current_Quarter, 2)
VAR Quarter_period =
    RIGHT ( Current_Quarter, 1 )
RETURN
    IF (
            Quarter_period = "1",
            CALCULATE (
                SUM ( Table2[Sales(m)] ),
               Table2[QuarterYear]
                    = ( Quarter_Year - 1 )
                    & "-"
                    & "Q"
                    & ( Quarter_period + 3 )
            ),
            CALCULATE (
                SUM ( Table2[Sales(m)] ),
                Table2[QuarterYear]
                    = Quarter_Year & "-"
                    & "Q"
                    & Quarter_period - 1
            )
        )

Regards,
Lydia

[rahulvyas]

My this year sales works with the formula but getting an error with Sales last year. 

[Myurathan]

Thank you so much for your help.