DAX Issue.

[Anonymous]

Hi  Experts,

When I'm trying to create a measure I stuck up with below issue.

Basicaly here I'm trying to display previous year values. I can use datediff but my column RPT Year is in Whole number Format

Test = CALCULATE(SUM(Query[Jan]),Query2[RPTyear]=2019) ----It works

But whenb I'm trying to do like below, I'm getting error(basically dynamic)

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

If we select 2020, i need to pass 2019. RPTYear is showing as slicer

Thanks,

Anand

[Anonymous]

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.

[AllisonKennedy]

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

[amitchandak]

@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

[ryan_mayu]

@Anonymous 

maybe you can try this

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

[Anonymous]

When I'm using selectedvalue function getting below error

Error:

A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[ryan_mayu]

@Anonymous 

you need to create a calendar table and use dateadd

https://docs.microsoft.com/en-us/dax/dateadd-function-dax

[Pragati11]

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]

It is displaying error as Can not find RPT year