cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harshadrokade
Helper III
Helper III

Return previous year comment on current year selection on slicer

Hi Helping hands,

 

When I select year 2021 on slicer, the visual should show the comment of the previous year (2020) as Bad. I tried using the below formula given but that is not working on text values

 

Previous year sales amount- CALCULATE(SUM('Sales data'[Sales]),DATEADD('Calendar'[Date],-1,YEAR))

 

Table name- Sales data

Columns-

Year   Sales  comment

2021   1111  Good

2020   2222   Bad

2019   3333   Bad

2018   4444   Better

2 ACCEPTED SOLUTIONS

Hi @harshadrokade ,

You can update the formula of measure [Prev Year Comment] as below:

Prev Year Comment =
CALCULATE (
MAX ( 'Sales data'[Comment] ),
FILTER (
'Sales data',
'Sales data'[Year]
= SELECTEDVALUE ( 'Calendar'[Date].[Year] ) - 1
)
)

yingyinr_0-1624264496970.png

Best Regards

Community Support Team _ Rena
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

Hi @harshadrokade ,

The attachment is my sample pbix file, please find it for the details.

Apply auto date/time in Power BI Desktop

yingyinr_0-1624324939874.png

Best Regards

Community Support Team _ Rena
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

7 REPLIES 7
TomMartens
Super User II
Super User II

Hey @harshadrokade ,

 

try creating a measure like so:

Prev Year Comment = 
var __SelectedYearFromSlicer = SELECTEDVALUE('Calendar'[Date].Year) - 1
retrun
CALCULATE(
    MAX('Sales data'[Comment])
    ,'Calendar'[Date].Year = __SelectedYearFromSlicer
)

Hopefully, this provides what you are looking for. If not consider creating a pbix that contains sample data but still reflects your data model, upload the pbix to onedrive or dropbox, and share the link. If you are using Excel to create the sample data share the xlsx as well.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

 @TomMartens Thanks for your reply sir. I tried the above Dax but it is giving me below error..

harshadrokade_0-1623870169477.png

Due to security reasons, my file upload is restricted & so I am unable to upload pbix/base data file. I have mentioned below the sample table names & column names that I have in excel.

Table- Sales data 

Year        Sales    Comment
2021      23           Good
2020      24           Bad
2019      25           Better
2018      26           Nice
2017      27           Very bad
2016      28           Awesom
2015      29           Ok
2014      30           Bad
2013      31          Good
2012      32          Ok
2011      33          Nice

 

A dimension is created for calendar with measure as below-

Calendar = CALENDAR(DATE(2015,01,01), DATE(2025,12,31))
 
I have created measure to see the previous year Sales as below-
P Sales = CALCULATE(SUM('Sales data'[Sales]),DATEADD('Calendar'[Date],-1,YEAR))
 
Request you to pls help sir..

 

Hi @harshadrokade ,

You can update the formula of measure [Prev Year Comment] as below:

Prev Year Comment =
CALCULATE (
MAX ( 'Sales data'[Comment] ),
FILTER (
'Sales data',
'Sales data'[Year]
= SELECTEDVALUE ( 'Calendar'[Date].[Year] ) - 1
)
)

yingyinr_0-1624264496970.png

Best Regards

Community Support Team _ Rena
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

HI @yingyinr Sir,

Can you pls help me to understand how did you create that Calendar table, Date column & Date Hierarchy? It would be very helpful if you can uplaod the PBIX file with examples sir. 

Hi @harshadrokade ,

The attachment is my sample pbix file, please find it for the details.

Apply auto date/time in Power BI Desktop

yingyinr_0-1624324939874.png

Best Regards

Community Support Team _ Rena
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

Thanks a lot

THanks a lot sir @yingyinr . Actually my organisation doesn;t allow uploads to external drive & so I couldn;t share my file. I will see your file & keep you posted. Thanks a lot.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors