cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Helper IV

## Based on year selection last year value show

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

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.

3 REPLIES 3
Highlighted
Super User IV

@RajeshRanganath , 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])) )

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper IV

@amitchandak   thanks for your response

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

Highlighted
Community Support

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.

## Helpful resources

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors