Order ID Instance Count

DAX DAX Calculations
1

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?

Intalment Order

Hope this makes sense :grimacing:

2

@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
3

@martynross85,

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.

image
image848×385 14.2 KB

EDNA Index Column by Order ID and InstalDate.pbix (33.5 KB)

Thanks
Jarrett

2 Likes
4

@AntrikshSharma,

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
5

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
6

Hi @AntrikshSharma,

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

7

Hi @JarrettM

Thanks for this - this has worked a treat.

Much appreciated to everyone who has contributed

Thanks

8

@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