Forum Discussion

klehar's avatar
klehar
Icon for Helper V rankHelper V
4 years ago
Solved

Calculate YoY without sameperiodlastyear

Hi Team,

 

I want to calculate YoY but I cant use sameperiodlastyear DAX since my date column is in the format yyyyqq. Let me know how I can do this.

 

YoY calculation : Current quarter sales compared to last year's sales of same quarter (Calculated for all quarters)
Sample Data :

Fiscal YearRegionSales
2021Q1APAC78
2021Q1North America84
2021Q1Europe58
2021Q2APAC99
2021Q2North America46
2021Q2Europe66
2021Q3APAC31
2021Q3North America100
2021Q3Europe77
2021Q4APAC100
2021Q4North America22
2021Q4Europe86
2022Q1APAC70
2022Q1North America96
2022Q1Europe94
2022Q2APAC54
2022Q2North America68
2022Q2Europe90
2022Q3APAC16
2022Q3North America64
2022Q3Europe25
2022Q4APAC92
2022Q4North America74
2022Q4Europe56

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  klehar ,

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _today=TODAY()
var _year=YEAR(_today)
var _qu="Q"&""&QUARTER(TODAY() )
return
IF(
   _year=MAX('Table'[Year])&&_qu=MAX('Table'[Quarter])
    ||
    _year-1=MAX('Table'[Year])&&_qu=MAX('Table'[Quarter])
    ,1,0)
    Qu_Total =
SUMX(FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Quarter]=MAX('Table'[Quarter])),[Sales])

    2. Place [Flag]in Filters, set is=1, apply filter.

    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

4 Replies

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hey, we can solve it as is, but it will require some text manipulations in the DAX. Can you at least create 2 addiotional columns of year and quarter No? Then I'll write you the code here.

    You won't need to put them in the visual, but just that they will be in your model table

    • klehar's avatar
      klehar
      Icon for Helper V rankHelper V
      4 years ago

      SpartaBI here it is

      Fiscal YearRegionSalesYearQuarter
      2021Q1APAC782021Q1
      2021Q1North America842021Q1
      2021Q1Europe582021Q1
      2021Q2APAC992021Q2
      2021Q2North America462021Q2
      2021Q2Europe662021Q2
      2021Q3APAC312021Q3
      2021Q3North America1002021Q3
      2021Q3Europe772021Q3
      2021Q4APAC1002021Q4
      2021Q4North America222021Q4
      2021Q4Europe862021Q4
      2022Q1APAC702022Q1
      2022Q1North America962022Q1
      2022Q1Europe942022Q1
      2022Q2APAC542022Q2
      2022Q2North America682022Q2
      2022Q2Europe902022Q2
      2022Q3APAC162022Q3
      2022Q3North America642022Q3
      2022Q3Europe252022Q3
      2022Q4APAC922022Q4
      2022Q4North America742022Q4
      2022Q4Europe562022Q4
  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    klehar 

    Sales Previous Quarter = 

VAR _currentyear = MAX('Table'[Year])
VAR _currentquarter = MAX('Table'[Quarter])
VAR _result = 
CALCULATE(
	[Sales],
	REMOVEFILTERS('Table'),
	'Table'[Quarter] = _currentquarter,
	'Table'[Year] = _currentyear - 1
)
RETURN
	_result
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  klehar ,

    Here are the steps you can follow：

    1. Create measure.

    Flag =
var _today=TODAY()
var _year=YEAR(_today)
var _qu="Q"&""&QUARTER(TODAY() )
return
IF(
   _year=MAX('Table'[Year])&&_qu=MAX('Table'[Quarter])
    ||
    _year-1=MAX('Table'[Year])&&_qu=MAX('Table'[Quarter])
    ,1,0)
    Qu_Total =
SUMX(FILTER(ALLSELECTED('Table'),'Table'[Year]=MAX('Table'[Year])&&'Table'[Quarter]=MAX('Table'[Quarter])),[Sales])

    2. Place [Flag]in Filters, set is=1, apply filter.

    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