Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
@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
My this year sales works with the formula but getting an error with Sales last year.
User | Count |
---|---|
101 | |
90 | |
78 | |
70 | |
69 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |