cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Myurathan Frequent Visitor
Frequent Visitor

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

I am fairly new to DAX. I have following table in the data model. 2017-09-13.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@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
            )
        )

1.JPG2.JPG3.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Moderator v-yuezhe-msft
Moderator

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

@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
            )
        )

1.JPG2.JPG3.JPG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Myurathan Frequent Visitor
Frequent Visitor

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

Thank you so much for your help.