topic Re: Using CALCULATE with variable in DAX Commands and Tips

Using CALCULATE with variable

knowparthshah — Wed, 08 Jan 2020 06:59:41 GMT

When I write this measure,

Amt CYPY =

VAR py = 2019

RETURN

CALCULATE (

[Amt], YEAR(Sales[Date]) >= py

)

the table shows values for only years greater than 2019 ( and empty against other years)

but when I use

Amt CYPY =

VAR py = [PreviousYear]

RETURN

CALCULATE (

[Amt], YEAR(Sales[Date]) >= py

)

it shows data for al years

PreviousYear = [CurrentYear] - 1

I want to make the 2019 in the above measure dynamic.

i.e. it takes max year of sales data as currentyear and m-1 as previous year

please help, I am new to Power bi

Re: Using CALCULATE with variable

az38 — Wed, 08 Jan 2020 07:18:17 GMT

knowparthshah

how do you calculate [CurrentYear]? Is it measure or calculated column?

do not hesitate to give a kudo to useful posts and mark solutions as solution

Re: Using CALCULATE with variable

Parkavi — Wed, 08 Jan 2020 07:19:34 GMT

knowparthshah Please make sure you have used the below DAX measure for [CurrentYear], which will return the max year in the table irrespective of any filters applied

CurrentYear =

CALCULATE(YEAR(MAX(Sales[Date])),ALL(Sales))

Re: Using CALCULATE with variable

knowparthshah — Wed, 08 Jan 2020 07:56:23 GMT

It is a measure

Year ( max ( Sales[Date]))

Re: Using CALCULATE with variable

knowparthshah — Wed, 08 Jan 2020 07:57:09 GMT

Thanks a lot, It worked