Slicer selection doesn't return correct value

[dokat]

Hi,

I have a data table in below format i have a slicer based on "Slicer Date" column.

I created two [TY] and [LY] measures to do year over calculations. However when slicer a value is selected in slicer it returns 

 TY: = Calculate(SUM('P&L'[Values]), ALLSELECTED('P&L'[Calendar Year]))

 LY = CALCULATE(SUM('P&L'[Values]),DATEADD('P&L'[Calendar Year],-1,YEAR))  

Chg %=[TY]/[LY]-1

 If there is no slicer selection formulas work seemlesly however when a date is selected whether YTD, Last Mont or Last Year. It return Value for 2022 but not previous year (2021). Can someone please help me with what i am doing wrong?

Calendar Year	P&L	Values	Slicer Date
12/31/2020	Sales	1000
12/31/2021	Sales	2000	Last Year
1/1/2021	Sales	3000
1/1/2022	Sales	4000	Last Month
1/31/2021	Sales	5000
1/31/2022	Sales	2500	YTD

[v-xiaotang · Accepted solution]

Hi @dokat 

Have you solved this question? Is there anything else I can help with? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.❤️

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@dokat You are going to likely need an ALL somewhere so that you can break out of the filter context set by the slicer. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[dokat]

I tried the previous year function rather than dateadd but still i can't get it to work. Is it possible that it is not linking to columns together Slicer Dat and Calendar Year columns. Essentially i am asking with [LY] to look up Slicer Date Value in Calendar Year column and go back 1 year. whether with dateadd function or previous year. For some reason it doesnt work. Appreciate any help.

[Greg_Deckler]

@dokat Yeah, I don't use any Time "Intelligence" functions. Because they are evil IMHO. If you are, make sure you have a separate date table and it is set to be a date table. There are alternative formulas for date intelligence in Power BI Cookbook 2nd Ed. that don't use TI functions.