Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
@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-
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
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.
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
Thanks a lot
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |