Calculate YoY without sameperiodlastyear

[klehar]

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 Year	Region	Sales
2021Q1	APAC	78
2021Q1	North America	84
2021Q1	Europe	58
2021Q2	APAC	99
2021Q2	North America	46
2021Q2	Europe	66
2021Q3	APAC	31
2021Q3	North America	100
2021Q3	Europe	77
2021Q4	APAC	100
2021Q4	North America	22
2021Q4	Europe	86
2022Q1	APAC	70
2022Q1	North America	96
2022Q1	Europe	94
2022Q2	APAC	54
2022Q2	North America	68
2022Q2	Europe	90
2022Q3	APAC	16
2022Q3	North America	64
2022Q3	Europe	25
2022Q4	APAC	92
2022Q4	North America	74
2022Q4	Europe	56

[v-yangliu-msft · Accepted solution]

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

[SpartaBI]

@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

[SpartaBI]

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]

@SpartaBI here it is

Fiscal Year	Region	Sales	Year	Quarter
2021Q1	APAC	78	2021	Q1
2021Q1	North America	84	2021	Q1
2021Q1	Europe	58	2021	Q1
2021Q2	APAC	99	2021	Q2
2021Q2	North America	46	2021	Q2
2021Q2	Europe	66	2021	Q2
2021Q3	APAC	31	2021	Q3
2021Q3	North America	100	2021	Q3
2021Q3	Europe	77	2021	Q3
2021Q4	APAC	100	2021	Q4
2021Q4	North America	22	2021	Q4
2021Q4	Europe	86	2021	Q4
2022Q1	APAC	70	2022	Q1
2022Q1	North America	96	2022	Q1
2022Q1	Europe	94	2022	Q1
2022Q2	APAC	54	2022	Q2
2022Q2	North America	68	2022	Q2
2022Q2	Europe	90	2022	Q2
2022Q3	APAC	16	2022	Q3
2022Q3	North America	64	2022	Q3
2022Q3	Europe	25	2022	Q3
2022Q4	APAC	92	2022	Q4
2022Q4	North America	74	2022	Q4
2022Q4	Europe	56	2022	Q4