cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

5 REPLIES 5
Microsoft Employee

Can you please give us a view of sample data?

Proud to be a Datanaut!

Frequent Visitor

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

Microsoft Employee

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

Frequent Visitor

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

Microsoft Employee

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