DAX Issue.

Hi Anonymous ,

First thing - You haven't mentioned what error you are getting using this measure. Share it to describe what the issue is?

Thanks,

Pragati

Anonymous 

maybe you can try this

Test = CALCULATE(SUM(Query[Jan]),Query2[RPTyear]=SLECTEDVALUE(Query2[RPTyear])-1))

Anonymous , In case you need this year vs last year. If you have date then use time intelligence. else create a year table and use the formula using only year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

//Only year vs Year, not a level below. This can work without the date table but need a year table

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

Anonymous  do you have a DimDate table? Is that what Query2 is? 

You can do this more easily if you have a DimDate table using built in time intelligence, otherwise you need to calculate the max selected year in the context of the entire report, you can do this using a variable, but does it need to work for multiple year selections? If so, DimDate table is much easier. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Otherwise, using a variable would look something like (I have tried to account for multiple year selections, and imitated behaviour of the DATEADD function using time intelligence and date table: 

Test =

VAR maxYear = MAX(Query2[RPTyear])

VAR minYear = MIN(Query2[RPTyear])

RETURN

CALCULATE(SUM(Query[Jan]),FILTER(ALL(Query2[RPTyear]), Query2[RPTyear]<= maxYear-1 && Query2[RPTyear]>= minYear-1 ))

Hi  Anonymous ,

According to your description, I created the data:

Here are the steps you can follow：

1. Use the calculated table to create a calendar table named query2, and create a calculated column to display the yearcalculated table：

Quert2 = CALENDARAUTO()

calculated column：

PRTyear = YEAR('Quert2'[Date])

2. Create measure.

Test =
var _select =SELECTEDVALUE('Quert2'[PRTyear])
return CALCULATE(SUM('Query'[Jan]),FILTER('Query',YEAR('Query'[Date])=_select-1))

3. Result

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

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