cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
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 

 

YearValueBusiness
201973,431.49Allocated
201921,088.96Consumer
201911,203.00Consumer
201958.07Expenses
202013,234.34Expenses
202075,690.32Allocated
202017,817.28Consumer
202046.46Expenses
201871,822.32Allocated
201818,293.38Consumer
201869,064.75Expenses

 

 

i need result like this

 

business CY ValuePY  Value
Allocated71,822.3273,431.49
Consumer17,817.2832,291.96
Expenses69,111.2113292.21

 

Many thanks

Rajesh

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Hi @RajeshRanganath ,

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] )
    )
)

Value.png

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.

 

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
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

@amitchandak   thanks for your response

 

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

 

 

Highlighted
Community Support
Community Support

Hi @RajeshRanganath ,

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] )
    )
)

Value.png

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.

 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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