cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsadams
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

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
Phil_Seamark
Microsoft
Microsoft

Can you please give us a view of sample data?


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

Proud to be a Datanaut!

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

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

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

 

 

v-ljerr-msft
Microsoft
Microsoft

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.