Based on year selection last year value show

[Anonymous]

Hi all,

i have 3 years data,  if i select 2020 ,  2020 data and 2019 needed, 

if i select 2019 , 2019 data and 2018 data needed

based on year selection last year data needed

sample data 

Year	Value	Business
2019	73,431.49	Allocated
2019	21,088.96	Consumer
2019	11,203.00	Consumer
2019	58.07	Expenses
2020	13,234.34	Expenses
2020	75,690.32	Allocated
2020	17,817.28	Consumer
2020	46.46	Expenses
2018	71,822.32	Allocated
2018	18,293.38	Consumer
2018	69,064.75	Expenses

i need result like this

business	CY Value	PY  Value
Allocated	71,822.32	73,431.49
Consumer	17,817.28	32,291.96
Expenses	69,111.21	13292.21

Many thanks

Rajesh

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create these two measures:

CY Value = SUM ( 'Table'[Value] )

PY Value = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year]
            = SELECTEDVALUE ( 'Table'[Year] ) - 1
            && 'Table'[Business] IN DISTINCT ( 'Table'[Business] )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Create a separate year(Date) Table

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling 2 = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

[Anonymous]

@amitchandak   thanks for your response

you mean create the date table then create the measure on sales table ?