Forum Discussion
Solved
YOY calculation
- If we select a specific year the comparison is vs. the previous year (full year) – for example, 2023 (YTD) vs 2022 (full year)
- If we select a specific year AND select a quarter in the slicer QQ the comparison is for specific quarter this year vs. the previous year (full year) – for example, Q1 2023 vs 2022 (full year)
- If we like to make a Q vs. Q - select a specific year AND QQ the comparison is vs. the previous selected quarter – for example, Q1 2023 vs. Q1 2022-->In order to create this comparison need to select specifically those 2 quarters in the slicer. If that’s what we have I will just make sure people are clear with it.
Hi YASHASHRI ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Table 2 = SUMMARIZE( 'Table','Table'[Year],'Table'[quarter])2. Create measure.
Measure = var _selectyear=SELECTEDVALUE('Table 2'[Year]) var _selectqu=SELECTCOLUMNS('Table 2',"1",[quarter]) var _count=COUNTX(ALLSELECTED('Table 2'),[quarter]) var _minqu=MINX(ALLSELECTED('Table 2'),[quarter]) var _maxqu=MAXX(ALLSELECTED('Table 2'),[quarter]) var _lastminqu= SUMX( FILTER(ALL('Table'),'Table'[Year]=_selectyear-1&&'Table'[quarter]=_minqu),[Value]) var _currentmaxqu= SUMX( FILTER(ALL('Table'),'Table'[Year]=_selectyear&&'Table'[quarter]=_maxqu),[Value]) var _YTD= SUMX( FILTER(ALL('Table'),'Table'[Date]>=DATE(YEAR(TODAY()),1,1)&&'Table'[Date]<=TODAY()),[Value]) var _fullyear= SUMX( FILTER(ALL('Table'),'Table'[Year]=_selectyear-1),[Value]) var _quarterthisyear= SUMX( FILTER(ALL('Table'),'Table'[Year]=_selectyear&&'Table'[quarter] in _selectqu),[Value]) return IF( _count=2,DIVIDE(_lastminqu,_currentmaxqu), IF( HASONEVALUE( 'Table 2'[quarter])&&HASONEVALUE('Table 2'[Year])&&_count=1,DIVIDE(_quarterthisyear,_fullyear), DIVIDE(_fullyear,_count) ))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hello YASHASHRI,
Could you please try:
Total Sales = SUM('YourTable'[Sales]) Selected Year = SELECTEDVALUE('YearTable'[Year]) Selected Quarter = SELECTEDVALUE('QuarterTable'[Quarter]) YoY Full Year YTD = VAR CurrentYearSales = CALCULATE([Total Sales], 'YearTable'[Year] = Selected Year) VAR PreviousYear = Selected Year - 1 VAR PreviousYearSales = CALCULATE([Total Sales], 'YearTable'[Year] = PreviousYear) RETURN DIVIDE(CurrentYearSales - PreviousYearSales, PreviousYearSales, 0) YoY Quarter Full Year = VAR CurrentQuarterSales = CALCULATE([Total Sales], 'YearTable'[Year] = Selected Year, 'QuarterTable'[Quarter] = Selected Quarter) VAR PreviousYear = Selected Year - 1 VAR PreviousYearSales = CALCULATE([Total Sales], 'YearTable'[Year] = PreviousYear) RETURN DIVIDE(CurrentQuarterSales, PreviousYearSales, 0) QoQ Selected Quarters = VAR CurrentQuarterSales = CALCULATE([Total Sales], 'YearTable'[Year] = Selected Year, 'QuarterTable'[Quarter] = Selected Quarter) VAR PreviousQuarter = Selected Quarter - 1 VAR PreviousQuarterSales = CALCULATE([Total Sales], 'YearTable'[Year] = Selected Year, 'QuarterTable'[Quarter] = PreviousQuarter) RETURN DIVIDE(CurrentQuarterSales, PreviousQuarterSales, 0)Hi YASHASHRI ,
I created some data:
Here are the steps you can follow：
1. Create calculated table.
Table 2 = SUMMARIZE( 'Table','Table'[Year],'Table'[quarter])2. Create measure.
Measure = var _selectyear=SELECTEDVALUE('Table 2'[Year]) var _selectqu=SELECTCOLUMNS('Table 2',"1",[quarter]) var _count=COUNTX(ALLSELECTED('Table 2'),[quarter]) var _minqu=MINX(ALLSELECTED('Table 2'),[quarter]) var _maxqu=MAXX(ALLSELECTED('Table 2'),[quarter]) var _lastminqu= SUMX( FILTER(ALL('Table'),'Table'[Year]=_selectyear-1&&'Table'[quarter]=_minqu),[Value]) var _currentmaxqu= SUMX( FILTER(ALL('Table'),'Table'[Year]=_selectyear&&'Table'[quarter]=_maxqu),[Value]) var _YTD= SUMX( FILTER(ALL('Table'),'Table'[Date]>=DATE(YEAR(TODAY()),1,1)&&'Table'[Date]<=TODAY()),[Value]) var _fullyear= SUMX( FILTER(ALL('Table'),'Table'[Year]=_selectyear-1),[Value]) var _quarterthisyear= SUMX( FILTER(ALL('Table'),'Table'[Year]=_selectyear&&'Table'[quarter] in _selectqu),[Value]) return IF( _count=2,DIVIDE(_lastminqu,_currentmaxqu), IF( HASONEVALUE( 'Table 2'[quarter])&&HASONEVALUE('Table 2'[Year])&&_count=1,DIVIDE(_quarterthisyear,_fullyear), DIVIDE(_fullyear,_count) ))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
