cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors