YOY calculation

[YASHASHRI]

1. If we select a specific year the comparison is vs. the previous year (full year) – for example, 2023 (YTD) vs 2022 (full year)
2. 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)
3. 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.

[Anonymous · Accepted solution]

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

[Sahir_Maharaj]

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)