Return previous year comment on current year selection on slicer

[harshadrokade]

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

[v-yiruan-msft · Accepted solution]

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

Best Regards

[TomMartens]

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

[harshadrokade]

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

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..

[v-yiruan-msft · Accepted solution]

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

Best Regards

[harshadrokade]

HI @v-yiruan-msft 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. 

[v-yiruan-msft · Accepted solution]

Hi @harshadrokade ,

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

Apply auto date/time in Power BI Desktop

Best Regards

[harshadrokade]

Thanks a lot

[harshadrokade]

THanks a lot sir @v-yiruan-msft . 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.