cancel
Showing results for
Did you mean:
Highlighted
Member

## Calculated 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 3
Regular Visitor

## Re: Calculated table with filters

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.

Member

## Re: Calculated table with filters

Hi @AWA

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

Member

## Re: Calculated table with filters

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.

Announcements

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 296 members 3,638 guests
Recent signins: