how to calculate sale amount difference between season of context and previous season?

[Anonymous]

I am triying to calculate sale amount difference between season of context and previous season.
But I don't know how to solve this problem.

Data is below:

Fact Table

SaleMonth	Season	SaleAmount
2018-06-01	U	604
2018-04-01	U	10
2018-10-01	U	322
2017-10-01	U	557
2016-03-02	U	12
2018-05-01	Q	49
2018-06-01	Q	948
2017-04-01	Q	134
2017-05-01	Q	427
2016-12-01	Q	578
2018-06-01	R	810
2018-06-01	R	431
2018-06-01	R	167
2017-12-01	R	935
2017-11-01	R	777
2018-08-01	W	447
2018-08-01	W	799
2018-07-01	W	133

Dimension Table

Season	SeasonYear	Categroy
Q	2017	S/S
R	2017	F/W
U	2018	S/S
W	2018	F/W

• "W" season's previous season is "R"
• "U" season's previous season is "Q"

I have to call sale amounts about previous season on current context.

I don't call values which are on difference rows.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create relationship as below

Create measures

previous = CALCULATE(SUM(Sheet1[SaleAmount]),FILTER(ALLEXCEPT(Sheet2,Sheet2[Categroy]),[SeasonYear]=MAX([SeasonYear])-1))

diff = SUM(Sheet1[SaleAmount])-[previous]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Actually I want to filter data by season only, not year.

But this solution is good as i filter the [Category].

Thank you very much!