Error with my measure while getting the count Year over Year

[Kalachuchi]

I'm trying to get the Year over year difference between the current year and the previous year but getting an error

Percentage Count = 
var _measure = DISTINCTCOUNTNOBLANK(Data[ENumber])
var _CurrYear = Format(FIRSTNONBLANK(Data[Year],"True"),"yyyy")
var _PrevYear = Format(FIRSTNONBLANK(Data[Year],"True")-1,"yyyy")

var _CalcCurrYear = Calculate(_measure,_CurrYear)
var _CalcPrevYear = Calculate(_measure,_PrevYear)
var _calc1 = _CalcCurrYear - _CalcPrevYear 
var _calc2 = Divide(_Calc1,_CalcCurrYear)

return _calc2​

The true/false expression does not specifcy a column

[Anonymous]

Hello @Kalachuchi ,

I am not able to understand, What you would like to do here.

var _CalcCurrYear = Calculate(_measure,_CurrYear)
var _CalcPrevYear = Calculate(_measure,_PrevYear)

[Kalachuchi]

I want to calculate the DISTINCTCOUNTNOBLANK(Data[ENumber]) by Format(FIRSTNONBLANK(Data[Year],"True"),"yyyy")

So if I have 100 on my 2021 and 40 on my 2020 then i should get 60 as the difference between the Year over Year

[Anonymous]

hello @Kalachuchi ,

try with this

ar _CurrYear = Format(LASTDATE(Data[Year]),"yyyy")
var _PrevYear = Format(PREVIOUSYEAR(Data[Year]),"yyyy")
var _CalcCurrYear = Calculate(DISTINCTCOUNTNOBLANK(Data[ENumber]),Data[Year]=_CurrYear)
var _CalcPrevYear = Calculate(DISTINCTCOUNTNOBLANK(Data[ENumber]),Data[Year]=_PrevYear)
var _calc1 = _CalcCurrYear - _CalcPrevYear
var _calc2 = Divide(_calc1,_CalcCurrYear)
return _calc2

If this doesn't solve your problem, could you able to share some data to better understand the problem.

Thanks

[Kalachuchi]

@Anonymous, The error was gone however the result are the same with the DISTINCTCOUNTNOBLANK(Data[ENumber]
Please check the screenshot below:

[v-kelly-msft]

Hi @Kalachuchi ,

try with this

Measure =

var _CurrYear = YEAR(MAX('Data'[Year]))

var _PrevYear = _CurrYear-1

var _CalcCurrYear = Calculate(DISTINCTCOUNTNOBLANK(Data[ENumber]),FILTER(ALL('Data'),YEAR('Data'[Year])=_CurrYear))

var _CalcPrevYear = Calculate(DISTINCTCOUNTNOBLANK(Data[ENumber]),FILTER(ALL('Data'),YEAR('Data'[Year])=_PrevYear))

var _calc1 = _CalcCurrYear - _CalcPrevYear

var _calc2 = Divide(_calc1,_CalcCurrYear)

return _calc2

I made a sample .pbix for reference,see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Kalachuchi]

@v-kelly-msft I tried this measure but it's not working it's only giving me zero values

I tried to return the _CurrYear, _PrevYear,_CalcCurrYear  and it's working. however, when I tried to retrun _CalcPrevYear I am getting the exact values from the distinct count of the year and the -1 of the year is not triggering

Instead of 
Calculate(DISTINCTCOUNTNOBLANK(Data[ENumber]),FILTER(ALL('Data'),YEAR('Data'[Year])=_PrevYear))

I tried static valuesCalculate(DISTINCTCOUNTNOBLANK(Data[ENumber]),FILTER(ALL('Data'),YEAR('Data'[Year])=2019))

And still the values are the same with the _CurrYear

[v-kelly-msft]

Hi  @Kalachuchi ,

Is your issue solved now?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft]

Hi @Kalachuchi,

If you use Calculate(COUNT(Data[ENumber]),FILTER(ALL('Data'),YEAR('Data'[Year])=_PrevYear)) or Calculate(COUNT(Data[ENumber]),FILTER(ALL('Data'),YEAR('Data'[Year])=2019)) instead,will you get the correct values?If not,could you pls provide some sample data?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!