Forum Discussion

YASHASHRI's avatar
YASHASHRI
Icon for Helper I rankHelper I
3 years ago
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...
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
3 years ago

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)