cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Myurathan
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
v-yuezhe-msft
Microsoft
Microsoft

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

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Microsoft
Microsoft

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

View solution in original post

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

Thank you so much for your help.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors