How to retrieve the date of when target sales was reached ?

[Tech]

Hi experts, 

I have the following tables :

What I would like to implement is a front end measure that displays the date of when target sales quantity (cumulatively speaking) was achieved.

With product_id 825 in my example, i want my measure to display "06-03-23", since it is on this date that the all time target sale quantity of 10 products was achieved (by reaching 11 in facts).

I have tried with FirstSortedValue() and RangeSum() but can't really find a way to make it work.

Due to very eratic sales pattern, linear interpolation between first sale date and last sale date is not a viable option. 

Has anyone any idea on how to achieve this ? 

Best Regards, 

Tech.

[cristianj23a · Accepted solution]

Hi, try this code:

Script: 

LOAD
sale_id,
sale_product_id,
Date#(sale_date, 'DD-MM-YYYY') as sale_date,
sale_qty
INLINE [
sale_id, sale_product_id, sale_date, sale_qty
234, 825, 01-01-2023, 5
980, 825, 06-03-2023, 6
456, 678, 12-03-2023, 7
];

Set analisys:

Date(min(aggr(
if(
RangeSum(
Above(
Sum(sale_qty),
0,
RowNo()
)
) >= 10,
sale_date
),
sale_product_id,
sale_date
)),'DD-MM-YYYY')

Regarts.

[Aurelien_Martinez · Accepted solution]

Hi,

My expression :

Date(Min(
  Aggr(
    if(rangesum( above( sum(sale_qty),0,rowno()))>target, sale_date), 
    sale_product_id,
    sale_date
  )
))

With this data :

data:
Load
  sale_id,
  sale_product_id,
  Date#(sale_date, 'MM-DD-YY') as sale_date,
  sale_qty
Inline [
  sale_id, sale_product_id, sale_date, sale_qty
  234, 825, 01-01-23, 5
  235, 825, 01-01-23, 2
  980, 825, 06-03-23, 6  
  980, 825, 08-03-23, 6
  456, 678, 12-03-23, 1
];

target:
Load
  *
Inline [
  sale_product_id, target
  825, 10
  678, 3
]

[Tech]

Thanks @cristianj23a , it worked like a charm ! 

I just changed the target to be dynamic (different for each product) and it worked. 

Thanks again, amazing to see how fast the community is !

Thanks also to @Aurelien_Martinez, both solutions are working. 

Tech