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

Accepted Solutions
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
)
)```

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

## 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
)
)```

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

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

Thank you so much for your help.