How to get order for the last period

[davidtanchon]

Hi, 

I would to write a formula that calculate the % order delay.

Each day we have a snapshot of open orders, and for each month I would like to calculate the % delay.

Meaning,  #of orders in delay/#of Orders

in delay = where Day GAP >0

#of Order = last order for each customer for the period (example here Order table id = 2,5,6

Meaning %delay will be 66% (2/3)

Order table id	Customer	Snapshot Day	Delivery day	Order ID	Qty	Day GAP
1	C1	01/01/2023	01/12/2022	A	1	31
2	C1	02/01/2023	01/12/2022	A	2	32
3	C2	01/01/2023	01/12/2022	B	1	31
4	C2	02/01/2023	01/12/2022	B	1	32
5	C2	03/01/2023	01/12/2022	B	1	33
6	C3	03/01/2023	31/03/2023	C	3	-87

thanks for your help

[johnt75]

You could use

% order delay = VAR LastOrdersPerCustomer = INDEX(
	1,
	'Table',
	ORDERBY( 'Table'[Order table id], DESC),
	PARTITIONBY( 'Table'[Customer])
)
VAR NumWithDelay = COUNTROWS(
	FILTER( LastOrdersPerCustomer, 'Table'[Day GAP] > 0)
)
VAR Total = COUNTROWS( LastOrdersPerCustomer)
VAR Result = DIVIDE( NumWithDelay, Total)
RETURN Result

[davidtanchon]

thanks, but it doesn't work. I have the following error message :

INDEX's Relation parameter may have duplicate rows. This is not allowed. 

[johnt75]

Go into the Modelling view, select the Table and then set the order table id column as the key column. That should get rid of the error.

[davidtanchon]

That's fixed the issue, but the result is wrong

[johnt75]

Couple of questions. What else is in the table or matrix visual? Anything that could be filtering the base table?

Is the order table ID column sorted in sequential order? If not, you may want to change the ORDERBY clause to use the snapshot day column instead.

[davidtanchon]

Hi,

Thanks for your questions, here are the answers and sur-questions :

What else is in the table or matrix visual? Anything that could be filtering the base table?

==> What do you mean? this matrix is just an example, the right data model is stored in SSAS and it's a little bit more complex. I just wanted to have an example of a measure that I can adapte.

Is the order table ID column sorted in sequential order? 

==> no, it was an example, but in my model the Table ID is not sorted

If not, you may want to change the ORDERBY clause to use the snapshot day column instead.

==> I did it but it doesn't work.

Thanks

[johnt75]

Not sure what the problem may be, the code worked with the sample data.

The underlying principal should work, you'll just need to adapt to fit your model. 

Use the INDEX function to get the latest row for each customer and you can then filter that for rows where Day Gap is not 0.

Just noticed that my code is checking for > 0, you may need to change that for <> 0.

[davidtanchon]

Hi, Is it possible to get this column "Attended Value", meaning 1 in the last record for an Order Number.

knowing my report is based on SSAS cube

Entry Date	Delivery Date	Order Number	Delay Value	Label Range Order Delay	Max	Attended Value
11/02/2023 00:00	10/02/2023 00:00	27574	1	Retard de 1 à 10 jours	2	0
12/02/2023 00:00	10/02/2023 00:00	27574	2	Retard de 1 à 10 jours	2	1
11/02/2023 00:00	10/02/2023 00:00	XH665	1	Retard de 1 à 10 jours	18	0
12/02/2023 00:00	10/02/2023 00:00	XH665	2	Retard de 1 à 10 jours	18	0
13/02/2023 00:00	10/02/2023 00:00	XH665	3	Retard de 1 à 10 jours	18	0
14/02/2023 00:00	10/02/2023 00:00	XH665	4	Retard de 1 à 10 jours	18	0
15/02/2023 00:00	10/02/2023 00:00	XH665	5	Retard de 1 à 10 jours	18	0
16/02/2023 00:00	10/02/2023 00:00	XH665	6	Retard de 1 à 10 jours	18	0
17/02/2023 00:00	10/02/2023 00:00	XH665	7	Retard de 1 à 10 jours	18	0
18/02/2023 00:00	10/02/2023 00:00	XH665	8	Retard de 1 à 10 jours	18	0
19/02/2023 00:00	10/02/2023 00:00	XH665	9	Retard de 1 à 10 jours	18	0
20/02/2023 00:00	10/02/2023 00:00	XH665	10	Retard de 1 à 10 jours	18	0
21/02/2023 00:00	10/02/2023 00:00	XH665	11	Retard de 11 à 20 jours	18	0
22/02/2023 00:00	10/02/2023 00:00	XH665	12	Retard de 11 à 20 jours	18	0
23/02/2023 00:00	10/02/2023 00:00	XH665	13	Retard de 11 à 20 jours	18	0
24/02/2023 00:00	10/02/2023 00:00	XH665	14	Retard de 11 à 20 jours	18	0
25/02/2023 00:00	10/02/2023 00:00	XH665	15	Retard de 11 à 20 jours	18	0
26/02/2023 00:00	10/02/2023 00:00	XH665	16	Retard de 11 à 20 jours	18	0
27/02/2023 00:00	10/02/2023 00:00	XH665	17	Retard de 11 à 20 jours	18	0
28/02/2023 00:00	10/02/2023 00:00	XH665	18	Retard de 11 à 20 jours	18	1

[johnt75]

It looks like you could use

Attended value = INT( 'Table'[Max] = 'Table'[Delay value])

[davidtanchon]

unfortunately, as my  report is based on SSAS model, INDEX function doesn't work.

I will post a new message with more details.