There is a scenario to find the total count of vendor based on the current year and the same customer should exist on the main transaction table.
Let me explain in detail.
Consider, I have a table called vendors which have very simple columns like below.
|Vendor ID||Vendor Name||Created Date|
|101||V1||Wednesday, February 22, 2017|
|102||V2||Thursday, December 28, 2017|
|103||V3||Friday, February 2, 2018|
|104||v4||Monday, May 7, 2018|
And Transaction table like below.
|Trans ID||Vendor ID||QTY||Sale Amount||OrderDate|
|1||101||5||1500||Tuesday, August 22, 2017|
|2||101||6||2588||Saturday, December 30, 2017|
|3||102||8||4500||Monday, April 2, 2018|
|4||103||12||8000||Thursday, June 7, 2018|
On the above tables, I want to show the total count of vendors where they are available in the transaction table and their created date should be the current year.
The expected result is “1” as Vendor 103 only available in Transaction table and created date is the current year 2018.
Rest of the rows are not matched with this scenario.
There are various methods are available to achieve the solution. In this post, I am explaining the very simple method.
Step 1: Create a new calculated column on the table Vendors using below script.
IsMatch = IF(Format(TODAY(),”YYYY”) = FORMAT(‘Customer (2)'[Date],”YYYY”), “Match”, “NoMatch”)
It is adding a new column with values like match and nomatch.
Step 2: Create another calculated column on the vendor table using below script. We need to use the above-created column.
Count = CALCULATE(IF(ISBLANK(MINX(RELATEDTABLE(‘FACT’),”1″)),0,1),’Customer (2)'[Test]=”Match”)
That’s it. We can use the count column for the result.