Forum Discussion
Post PatronCalculated table with filters
I have a dataset that displays customers' orders each day, with a column to also determine the customer's orderSequenceNumber i.e. on their first order this is 1, on their next order this is 2 and so forth.
Sample dataset
| Year | Month | Day | CustomerID | CustomerOrderSeqNo |
| 2019 | September | 14 | abc123 | 1 |
| 2019 | September | 16 | abc123 | 2 |
| 2019 | September | 20 | abc123 | 3 |
| 2019 | September | 21 | abc123 | 4 |
| 2019 | September | 26 | abc123 | 5 |
Using this sequenceNo, I will define whether the customer is new or existing. On 14th September, the customer is considered 'new' (denoted by the number 1) but on 16th Septemer the customer is now 'existing'.
However, if in my filter I were to select multiple dates (e.g. 14th, 16th and 20th), I would like to see whether the customer was either new or existing within this selected period. In this case, they are considered 'new' since their minimum sequenceNo is 1.
If filter was just 16th and 20th, they will be considered 'existing' since their minimum sequence number is greater than 1.
I would like to then create a filter for new and existing customers which would change dependant on date filter.
Does anyone know if this is possible?
Many thanks!
afk
3 Replies
Hi
I'm not sure if i've understood your problem fully, but try the following.
Create a new column with the following calculation:IF CustoemrOrderSeqNo = 1 THEN "New" ELSE "Existing" ENDIF
Now you can make a single-select slicer and choose whether you want to see new or existing customers in your list. So first choose date interval, then choose if you want to see new or existing customers in your visualization.
Let me know if this helps.
bo_afkHi Anonymous
Thanks for your suggestion.
Unfortuantely this doesnt work in my case since the status of the customer changes according to the filter selection of the date. The column only provides a static view of the customer's status at just one point in time whereas mine needs to be dynamic.
afk
v-joesh-msftSolution Sage
Hi bo_afk ,
For a dynamic view of the customer's status, I think you can create a measure like this:
Measure 2 = RANKX(FILTER(ALLSELECTED('Test'),Test[CustomerID]=MAX(Test[CustomerID])),CALCULATE(SUM('Test'[date])),,ASC)
The results are as follows:
Here is a demo , please try it：
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
