Count of orders after the last claim date

[Andrew_na_lang]

Hi Power Bi Family!

I need to identify the number of orders after the last claim submission date to today.

I have an orders table (fact table) where all orders are populated.  In the orders table, I have the unique order ids and the date of each order. My idea is to count the order ids or the order dates after the last claim submission. I just confused on what measure to use. 

ANy help is much appreciated!

 

[Anonymous · Accepted solution]

Hi  @Andrew_na_lang ,

I created some data:

CustomerTable：

Table：

Here are the steps you can follow：

1. Create measure.

Flag =
var _max1=MAXX(FILTER(ALL(CustomerTable),'CustomerTable'[Customer]=MAX('Table'[Customer])),'CustomerTable'[Date])
return
IF(
    MAX('Table'[Date])>_max1,1,0)

Sum = 
SUMX(FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])&&'Table'[ID]>=MIN('Table'[ID])),[Flag])

Measure =
IF(
    [Sum] <>0,[Sum]&" "&"time","No more than")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Andrew_na_lang]

@ribisht17 Is there a way to get the same result without merging the tables? I am working on a huge dataset and not ideal to merge them.

[ribisht17]

If you are using Column then use EARLIER instead of MAX

Count with Columns = CALCULATE(

COUNT('Order Customer Merge'[Customer ID]),

                                            FILTER(all('Order Customer Merge'),'Order Customer Merge'[Customer                                                              ID]=EARLIER('Order Customer Merge'[Customer ID]) && 'Order Customer Merge'[Customer.Last Claim Sub Date] < 'Order Customer Merge'[Order Date] ))

OR if it is still not working?

Is it possible to share the sample PBIX, difficult to say it from here?

Regards,

Ritesh

[Andrew_na_lang]

@ribisht17 I cannot share the pbx file, for it contains customer information. 

Count of Order after Sub = CALCULATE(

                                                           COUNT('Order Customer Merge'[Customer ID]),

                                                               FILTER(all('Order Customer Merge'),'Order Customer Merge'[Customer ID]=max('Order Customer Merge'[Customer ID]) && 'Order Customer Merge'[Customer.Last Claim Sub Date] < 'Order Customer Merge'[Order Date] ))


'Order Customer Merge'[Customer ID] -  You are referring to the orders table (order id column)?
'Order Customer Merge' - Is this my order's table?

[ribisht17]

Trying to simplify it ,

Step 1. Create a Column on your Order Table to Get the Customer's Last Claim Date

Last Claim Date -Customer = RELATED(Customer[Last Claim Sub Date])

Step.2 

M-New Orders Count = CALCULATE(

COUNT('Order'[Customer ID]),

                    FILTER(all('Order'),'Order'[Customer ID]=MAX('Order'[Customer ID]) && 'Order'[Last Claim Date -Customer] < 'Order'[Order Date] ))

Hope it helps!!

Regards,

Ritesh

Mark my post as a solution if it helped you| Gentlemen & Ladies|Munde and Kudis| I like your Kudos!! !!
My YT Channel Dancing with Data !! Connect on Linkedin || Power BI for Tableau Users 

[Andrew_na_lang]

@ribisht17 I did this.

On my customer's table, I created a new column - 

M-New Orders Count = CALCULATE(

COUNT('Order'[Customer ID]),

                    FILTER(all('Order'),'Order'[Customer ID]=MAX('Order'[Customer ID]) &&'Order'[Last Claim Date -Customer]<'Order'[Order Date]))

Here is how I ended up.

 

[ribisht17]

 Sample data is needed for both the tables so that solution can be provided, but I created a column on Orders, not Customers.

You can see what I did, last tab COUNT - Google Drive

[ribisht17]

You should be able to do that even without merging,logic remains the same, I can relate the table and do the same thing, did you try ?

Regards,

Ritesh

[Andrew_na_lang]

Hey Man @ribisht17,

Yes, I tried this measure as a calculated column. It was just loading and takes time to show results. 

 

There might be something off with my measure though. 

[ribisht17]

@Andrew_na_lang ,

Customer

Customer ID	Last Claim Sub Date
1	01-01-2022
2	02-01-2022
3	03-01-2022
4	04-01-2022

Order

Order ID	Order Date	Customer ID
1	02-01-2022	1
2	03-01-2022	1
3	04-01-2022	2
4	02-01-2022	2

I had to generate some data on my own, I did create 2 tables and merged it

As you can see Customer 1 ordered twice after the last day whereas Customer 2 ordered only once

This is the calculation I did use here,

Count of Order after Sub = CALCULATE(

                                                           COUNT('Order Customer Merge'[Customer ID]),

                                                               FILTER(all('Order Customer Merge'),'Order Customer Merge'[Customer ID]=max('Order Customer Merge'[Customer ID]) && 'Order Customer Merge'[Customer.Last Claim Sub Date] < 'Order Customer Merge'[Order Date] ))

Let me know if it helps

Regards,

Ritesh

Mark my post as a solution if it helped you| Gentlemen & Ladies|Munde and Kudis| I like your Kudos!! !!
My YT Channel Dancing with Data !! Connect on Linkedin || Power BI for Tableau Users 

[Andrew_na_lang]

Additional Information...


This is my customer table which has a column that shows the last claim submission date. 

Below is my orders table which has the unique order ids and the time created id (when the order was submitted). 

Basically, I need to know if the customer still submitted another order after the last claims submission date from my customer's table, and if yes, how many orders (count) were submitted after the last claims submission date to date. 

 

[Andrew_na_lang]

@ribisht17 


This is my customer table which has a column that shows the last claim submission date. 

Below is my orders table which has the unique order ids and the time created id (when the order was submitted). 

Basically, I need to know if the customer still submitted another order after the last claims submission date from my customer's table and if yes, how many orders where submitted after the last claims submission date to date. 

 

[ribisht17]

Post some data, please

Regards,

Ritesh