# of times a unique value appears over a date period

[jsadams]

I am working with multiple sites to see how often unique accounts transact at different locations.

I have 3 tables that are already related, one has the transaction date, location and account number. Another is a table of locations and different date ranges indicating construction and turnover. The third table is Primary key connecting the two together as they are from different sources.

 I have been able to complete this on a small scale using countifs(account#,unique account,"transaction date" (index match to the table with location #'s and their respective construction dates >= and <= to constrain within the 12 month period. The format of this table would be an array where column axis is account #'s and Row axis is location #.

Does anyone have any ideas on how to create a formula that will populate the number of transactions that each account had at respective locations 12 months prior to construction and 12 months post?

[Phil_Seamark]

Can you please give us a view of sample data?

[jsadams]

below is the list of column names associated with the 3 tables I reference in my original question and condensed example of data tables.

Table 1: Account #, Frequent location #, transaction location #, date of transaction

account #2	frequent location	transaction location	transaction date
1101000000004490.00	52	802	1/7/2012
1101000000004490.00	52	802	2/28/2012
1101000000004490.00	52	92	11/21/2014
1101000000004490.00	52	92	2/28/2016
1101000000004490.00	52	52	4/4/2012
1101000000004490.00	52	52	4/5/2012
1101000000004490.00	52	52	4/19/2012
1101000000004490.00	52	52	7/19/2012

table 2: Location #, "Other location moniker"

location #	other moniker
52	9191
802	3457
6522	990995

table 3: "other location moniker", Construction Date, Turnover date.

Other moniker	Const. Date	T/O Date
9191	6/10/2012	7/1/2014
3457	7/10/2015	6/14/2016
990995	4/11/2013	6/12/2013

I have been successful in creating a count If I hardcode in the date restraints, but haven't figured out a way to include a lookup to constrain the dates within another table.

Best,

JA

[v-ljerr-msft · Accepted solution]

Hi @jsadams,

---

I have been successful in creating a count If I hardcode in the date restraints, but haven't figured out a way to include a lookup to constrain the dates within another table.

---

You should be able to use RELATED function(DAX) to get a related value from another table.

And the formula below to calculate the number of transactions against your sample data is for your reference.

Number Of Transactions = 
CALCULATE (
    COUNT ( Table1[account #2] ),
    FILTER (
        Table1,
        ABS (
            (
                YEAR ( Table1[transaction date] ) * 12
                    + MONTH ( Table1[transaction date] )
            )
                - (
                    YEAR ( RELATED ( Table3[Const. Date] ) ) * 12
                        + MONTH ( RELATED ( Table3[Const. Date] ) )
                )
        )
            <= 12
    )
)

Note: Make sure there are corresponding relationships created for the three tables like below.

Here is sample pbix file.

Regards

[jsadams]

I know this thread has not been touched in a few weeks, but I have a follow up question regarding using the filter/related filters. How is this filter being used against my "construction/Turnover" table where there are multiple locations listed? How can I ensure the data is filtering off of a specific locations construction and turnover dates for everything in the matrix?

I've been asked to expand this work to a much larger set of data. I now need to filter out many of the customers that did not exist in both pre and post construction time. The only way I can do this as a formula and not hard coding is nearly the same as the initial method, except these dates are all in the same table, but I am struggling to derive the correct filters. For now, I have just filtered during the initial query instead of importing the raw data and using a formula.

The New Transaction table is:

Account activation date l account # l Account closed date l Transaction Date l transaction location.

I can post sample table if needed, but will have to chop the data down.

Best,

JA

[v-ljerr-msft]

Hi @jsadams,

According to your description above, you should be able to use COUNTA, CALCULATE, FILTER Function (DAX) to create a measure to get the number of transactions for each account with corresponding conditions, then show the measure with the account column and location column in a Matrix visual on the report. 

However, without understanding your real table structure, it's hard to write the formula for you. So could you post your table structures with some sample data in this case.

Regards