Previousyear excluding a filter

[DouweMeer]

To start, I received a deadline for Friday and have been breaking my head over this for the past few weeks. 

My problem is dat previousyear works as you expect. Within its own context it makes a calculation of this. So if you have sold pieces in region 1, 2 and 3 from this year and past year and you stuff the measure in a matrix where on row level is the year and regions, it calculates it without any problem. 

Now the problem starts. There is an additional filter to show whether current year sales is actually 'maintained'. Maintained is calculated whether the product was sold previous year based on a monthy YTD calculation. This verification is put in the raw data table where the measures are running on. The problem is that now 'new' sales with a calculation of previousyear do have sales. This since it makes the calculations of previous year on the context of the filter in the raw data table. If 2019 had an X amount of sales on 'new' products, the last year sales measure for 2020 on 'new' products, will sum to the amount of X in 2019. However, these X sales is rather for 2020 considered as 'maintained' sales or lost sales, but never 'new'. 

I was hoping someone could tell me. I've trying to redesign my tables, work with different relationships but all to no avail.

Original measure:

Sales (LY) =

VAR a1 = max ( 'Price Realization (2)'[Year] )

VAR a2 =

calculate (

sum ( 'Price Realization (2)'[FXN sale EUR] )

, filter (  all ( 'Date table PR (2)'[Date] ) , year ( 'Date table PR (2)'[Date] ) = a1 - 1 )

)

RETURN

a2

Changed:

Sales (LY) =

VAR a1 = max ( 'Price Realization (2)'[Year] )

VAR a2 =

calculate (

sum ( 'Price Realization (2)'[FXN sale EUR] )

, all ( 'Date table PR (2)'[Date] ) , all ( 'Price Realization (2)'[Maintain TY YTD] ) , year ( 'Date table PR (2)'[Date] ) = a1 - 1

)

RETURN

a2

[amitchandak]

Can you share sample data and sample output.

[DouweMeer]

@amitchandak 

Sort of, can't share you a pbix due to restriction. 

Test table:

Year	Product	Sales	Business
2018	A	5	New
2018	A	3	New
2018	B	8	New
2018	A	17	New
2018	A	21	New
2019	A	12	Maintain
2019	A	15	Maintain
2019	B	2	Maintain
2019	C	8	New
2020	A	5	Maintain
2020	B	3	Maintain
2020	C	18	Maintain
2020	D	20	New

Ass you can see, for 2019 there is maintained sales, however, for 2018 there wasn't any 'maintained' sales, only 'new' sales. Both products A and B had been sold in 2018 and thus should populate the number of Sales LY for maintained business in 2019. 

[amitchandak]

@DouweMeer ,Please check: https://www.dropbox.com/s/vighl8efolwisj4/previousYear_yearand%20date.pbix?dl=0

I converted to date and used time intelligence

[DouweMeer]

@amitchandak 

Was one of my attempts as well. What you get is due to the 1 to many relationship one way that products duplicate in a year. Once as a maintain and once as 'new'. The problem lies in the previousyear once again. 

A is an example, B has this for 2019 as well. C has it in 2020 as it is the first year it is 'maintained'.