Count the number of vendors which had transactions in selected years

[Newbie777]

Hi,

I'd like to write a measure formula which only counts the number of vendors which the company had transactions in a selected years and not count all the vendors in the vendor master data.  The formula below is producing error message. 

=count(if(aggr(
sum({<[Year]={$(=max([Year]))} >} [Amount in local currency]),[Vendor name]))<>0
,distinct([Vendor name]),0))

I'd appreciate it if you could let me know how I can fix the formula.  

Thank you!

[rwunderlich · Accepted solution]

Count({<[Vendor Name] = {"=Sum({<[Year]={$(=max([Year]))>}[Amount in local currency]) > 0"}>} Distinct [Vendor Name])

-Rob

[Newbie777]

Thanks a lot for your advice.  I copied and pasted your measure formula into the app, and the answer yielded is 0, even though vendor name count should be a different number.  I am going in the right direction, but I am wondering what is wrong still.  

[Kushal_Chawda · Accepted solution]

@Newbie777  you have syntax error in your expression. You are missing "}"  as highlighted below

Count({<[Vendor Name] = {"=Sum({<[Year]={$(=max([Year]))}>}[Amount in local currency]) > 0"}>} Distinct [Vendor Name])

[rwunderlich]

Can you replicate this by hand? Meaning make a chart with dimension = [Name 2] and Measure = Sum([FBL3N by employee.Amount in local currency]. Then select Year=2021 in selections.  

-Rob

[Newbie777]

Excellent.  I added the curly bracket missing and the formula works now.  Thanks a lot!