topic Re: Year Over Year Measure with Year Filter as Slicer in DAX Commands and Tips

Year Over Year Measure with Year Filter as Slicer

sqlguru448 — Thu, 17 Oct 2019 22:15:21 GMT

Hello Guys,

I am trying to calculate Year over Year sales with Year as a slicer. I am able to achieve it when I have consecutive years but when I try to filter on year slicers with gap then it gives me incorrect result.

Below is the data I want to achieve when Year slicer is selected as 2016,2018. 1st table gives me correct output when no filter is applied on the Year slicer, but when the slicer is filtered on Year 2016 and 2018, I need to display the output in table 2. Please help.

Year	Current Year Sales	Prior Year Sales
2016	1000	0
2017	2000	1000
2018	3500	2000
		Expected output for prior sales
Year	Current Year Sales	Prior Year Sales
2016	1000	0
2018	3500	1000

Thank you

Re: Year Over Year Measure with Year Filter as Slicer

kentyler — Mon, 14 Oct 2019 03:43:30 GMT

You need your filter to pick up the multiple months from the slicer. I found this solution in another post.

I guess if you replace your estimation measure

EstimateCurrentMonth = calculate(sum('Sales'[Estimate]);FILTER(ALL('Sales'[MonthNumber]);[MonthNumber] = 'MonthParameter'[MonthParValue]))

with this

EstimateCurrentMonth = 
var selectedMonths = Values('MonthParameter'[MonthParValue])
return
calculate(
	sum('Sales'[Estimate])
	;FILTER(
		ALL('Sales'[MonthNumber])
		;[MonthNumber] IN selectedMonths
	)
)

https://community.powerbi.com/t5/Desktop/Select-multiple-values-from-slicer-for-DAX-formula/td-p/500928

Re: Year Over Year Measure with Year Filter as Slicer

sqlguru448 — Mon, 14 Oct 2019 20:23:52 GMT

kentyler thank you for responding but this doesn't give me Prior year when I select different years.

Re: Year Over Year Measure with Year Filter as Slicer

kentyler — Mon, 14 Oct 2019 20:27:11 GMT

I should have said, you need to pick up multiple years from the slicer

the code examples show the pattern for doing that, but you have to change the table/column names

Re: Year Over Year Measure with Year Filter as Slicer

sqlguru448 — Mon, 14 Oct 2019 20:35:28 GMT

Yes, but this gives me only current month and current year, I don't get the prior year for the selected year 2016, 2018.

kentyler wrote:
I should have said, you need to pick up multiple years from the slicer

the code examples show the pattern for doing that, but you have to change the table/column names

Re: Year Over Year Measure with Year Filter as Slicer

sqlguru448 — Thu, 17 Oct 2019 22:30:37 GMT

Tried to do multiple ways but if the year slicers is not selected consecutive years then the dateadd, sameperiodlast year functions are not working. Any help is really appreciated.

Below is the DAX which I am working on.

1) Count YR Slicer = CALCULATE (

COUNTROWS ( FILTERS ( 'DDM D_DATE'[YEAR_NUMBER]))|

ALLSELECTED ( 'DDM D_DATE')

)

2) Max Yr Slicer values = CALCULATE(topn(1|VALUES('DDM D_DATE'[YEAR_NUMBER])|'DDM D_DATE'[YEAR_NUMBER]|DESC)|ALLSELECTED('DDM D_DATE'))

3) Min Yr Slicer values = CALCULATE(topn(1|VALUES('DDM D_DATE'[YEAR_NUMBER]))|ALLSELECTED('DDM D_DATE'))

4) Custom Paid Amount LY =

var countdiff_2 = If( [Count YR Slicer] = 2 && [Max Yr Slicer values] - [Min Yr Slicer values] = 2|1|0)

var MaxYrSlicer = CALCULATE(topn(1|VALUES('DDM D_DATE'[YEAR_NUMBER])|'DDM D_DATE'[YEAR_NUMBER]|DESC)|ALLSELECTED('DDM D_DATE'))

RETURN