cancel
Showing results for
Did you mean: 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. 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?

1 ACCEPTED SOLUTION  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
)
)   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.
3 REPLIES 3  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
)
)   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. Frequent Visitor

My this year sales works with the formula but getting an error with Sales last year. Frequent Visitor
Thank you so much for your help.  