Calculated table with filters

[bo_afk]

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

[Anonymous]

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_afk]

Hi @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-msft]

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：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfcmsZK-GDBDqpDK5p...

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.