cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

# of times a unique value appears over a date period

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: # of times a unique value appears over a date period

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.

relationship.PNG

 

Here is sample pbix file.Smiley Happy

 

Regards

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: # of times a unique value appears over a date period

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.Smiley Happy

 

Regards

Highlighted
Microsoft
Microsoft

Re: # of times a unique value appears over a date period

Can you please give us a view of sample data?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted
Frequent Visitor

Re: # of times a unique value appears over a date period

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 #2frequent locationtransaction locationtransaction date
1101000000004490.00528021/7/2012
1101000000004490.00528022/28/2012
1101000000004490.00529211/21/2014
1101000000004490.0052922/28/2016
1101000000004490.0052524/4/2012
1101000000004490.0052524/5/2012
1101000000004490.0052524/19/2012
1101000000004490.0052527/19/2012

table 2: Location #, "Other location moniker"

location #other moniker
529191
8023457
6522990995

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

Other monikerConst. DateT/O Date
91916/10/20127/1/2014
34577/10/20156/14/2016
9909954/11/20136/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

Highlighted
Microsoft
Microsoft

Re: # of times a unique value appears over a date period

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.

relationship.PNG

 

Here is sample pbix file.Smiley Happy

 

Regards

View solution in original post

Highlighted
Frequent Visitor

Re: # of times a unique value appears over a date period

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 account # l Account closed dateTransaction Date l transaction location.

 

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

 

Best,

JA

 

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors