# Order ID Instance Count

Hi All

Wondering if anyone knows how to do the following?

Where i have an Order Value of say £500 split over 5 months - i would have a monthly value of £100.

How can i create a count of the number of instances that occur per order id Line - i .e. instalment 1, Instalment 2, instalment 3, instalment 4 instalment 5?

Hope this makes sense

@martynross85 I am assuming you want this as a calculated column? In that case use this:

``````Column =
VAR CurrentDate = Table[InstalDate]
VAR RankByDate =
FILTER ( DISTINCT ( Table[InstalDate] ), Table[InstalDate] >= CurrentDate )
VAR Result =
COUNTROWS ( RankByDate )
RETURN
Result``````
4 Likes

Here is another way of doing it in Power Query without having to use a calculated column. There is a great video on this technique. I’ll include the link below.

Thanks
Jarrett

2 Likes

I’ve always done this in Power Query ala Jarrett’s solution, but I think your DAX solution is ingenious and I’ve posted it as a community entry in Analyst Hub (with attribution, of course).

• Brian
1 Like

Hi,
A slightly different approach to your question. My actual use case was about having a fact table and a table with transportation costs, object was to distribute the costs evenly by weight split across the multiple items in one shipment.
Example splitting costs dynamically.xlsx (19.6 KB)
Paul

1 Like

Thanks for looking into this for me - much appreciated.

This appears to work, however it looks as though it is providing a value from 1 to xxxx for all order id’s rather than 1 to xxxx for that specific / unique ID.

Think i need to bolt on another filter somewhere in this.

thanks for your help & contribution

Thanks for this - this has worked a treat.

Much appreciated to everyone who has contributed

Thanks

@martynross85 Oh I forgot about that part.

``````Column =
VAR CurrentDate = Table[InstalDate]
VAR CurrentOrderID = Table[OrderID]
VAR RankByDate =
FILTER (
ALL ( Table[InstalDate], Table[OrderID] ),
Table[InstalDate] >= CurrentDate
&& Table[OrderID] = CurrentOrderID
)
VAR Result =
COUNTROWS ( RankByDate )
RETURN
Result``````