Forum Discussion
Post PatronShow repeat customers within a week
I need to flag tickets that are for the same customer if that customer had a previous ticket within a week.
In Table A I have Account Number and in Table B I have the Ticket Number as well as the Ticket Date.
My issue is that when I try to use something like EARLIER I can only reference one table. So I can say acct = earlier acct but I than can't say ticket date <= earlier ticket date and vise versa.
In my head the solution would look something like this where I get a true statement if the same customer had a repeat ticket within 7 days.
Calculate
count(Ticket_ID),
Account_Num = Earlier(Account_Num),
DateDiff(Ticket_Date, Earlier(Ticket_date), Day) <=7
) > 0Any help or suggestions are appreciated.
Hi GunnerJ
Please refer to the linked discussion:
(the part of repeat in the suggested solution)
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
9 Replies
- Ashish_Mathur
Super User
Hi,
Not sure of whether you want to write a measure or a calculated column? Share the tables in a format that can be pasted in an MS Excel file and show the expected result.
GunnerJAshish_Mathur Ritaf1983
Here is a linked file. I have a table that is filtered to one account where all of the ticket occurance dates are within 7 days of each other except for the last ticket which is 8 days from the last ticket for that customer. My goal is to have all of the tickets within 7 days of the last ticket to be flagged.- Ashish_Mathur
Hi,
In calculating the difference of 8 hours, do you want to consider the time stamp as well or only the date?
- mh2587
Flag = VAR CurrentAccountNum = TableB[Account Number] VAR CurrentTicketDate = TableB[Ticket Date] VAR PreviousTickets = FILTER( TableB, TableB[Account Number] = CurrentAccountNum && TableB[Ticket Date] < CurrentTicketDate && TableB[Ticket Date] >= CurrentTicketDate - 7 ) RETURN IF( COUNTROWS(PreviousTickets) > 0, "Yes", "No" ) /*After adding this calculated column in Table B, you can use it to flag the tickets for the same customer with previous tickets within a week. If the "Flag" column is "Yes," it means that the customer had a previous ticket within a week; otherwise, it's "No." Please adjust the column and table names in the formula to match your actual data structure.*/- GunnerJ
mh2587 Thanks for the reply. My issue is I can't get the values from the two different tables to appear in the same dax. In the code you provided Account# and Ticket Date are actually in seperate tables so when I try and make sure the accounts are the same or I try to compare the dates I can only do one. I've attached a sample file below if you cared to take a look. In the bottom center table all tickets besides the first and last ticket should be flagged as they are within 7 days of the previous ticket for that account. Thanks!
https://www.dropbox.com/scl/fi/rs0opdqye10ujzay5azse/NEW-TMS-TEST.pbix?rlkey=noezwm2tcv8h8z5vw3dukmx2w&dl=0
