OPEN ORDERS

[Voyageur83]

Hi,

Suppose the sample datas attached. Using this data we would like a measure that tracks open orders
so that at each date point on a line chart we are looking at Available qty orders open 30 days prior to the required date.

Thanks

DATE_REQUIRED	QTY_ORDERED	QTY_DELIVERED	DATE_ORDER	PRODUCT	DATE_DELIVERED
2-Jan-24	270	283	8-Dec-23	P98000	18-Dec-23
3-Jan-24	1	0	3-Jan-24	P25717
3-Jan-24	14616	14616	11-Dec-23	P33792	3-Jan-24
3-Jan-24	3408	3408	11-Dec-23	P33793	3-Jan-24
3-Jan-24	1	324	30-Aug-23	P99947	4-Jan-24
3-Jan-24	800	1010	30-Aug-23	P100017	4-Jan-24
3-Jan-24	11798	11798	8-Dec-23	P103531	3-Jan-24
3-Jan-24	11484	11484	8-Dec-23	P104107	3-Jan-24
3-Jan-24	174	168	8-Dec-23	P104530	3-Jan-24
3-Jan-24	2128	2143	22-Dec-23	P108183	5-Jan-24
3-Jan-24	2128	2128	22-Dec-23	P108184	5-Jan-24
3-Jan-24	8464	8972	22-Dec-23	P108185	15-Jan-24
3-Jan-24	414	414	18-Dec-23	P108945	5-Jan-24
3-Jan-24	4	4	18-Dec-23	P108946	5-Jan-24
3-Jan-24	146	152	22-Dec-23	P109908	24-Jan-24
3-Jan-24	952	952	22-Dec-23	P110046	15-Jan-24
3-Jan-24	1476	1476	22-Dec-23	P110176	10-Jan-24
3-Jan-24	36	0	11-Dec-23	P110953
3-Jan-24	6	0	3-Jan-24	P111160
3-Jan-24	84	0	11-Dec-23	P111161
3-Jan-24	324	0	7-Dec-23	P112926
3-Jan-24	324	0	7-Dec-23	P113227
3-Jan-24	540	0	8-Jan-24	P113811
3-Jan-24	2160	0	6-Dec-23	P116871
3-Jan-24	276	0	30-Nov-23	P117578
3-Jan-24	276	0	30-Nov-23	P117873
3-Jan-24	276	0	30-Nov-23	P118161
3-Jan-24	3601	0	8-Jan-24	P118339

[Voyageur83 · Accepted solution]

Thank you for the help.

I use a pattern from SQLBI Computing open orders,  and update it from my need.

[Kedar_Pande]

@voyageur

Create a measure:

Open Orders (30 Days Prior) =
VAR SelectedDate = MAX('DateTable'[Date])
VAR StartDate = SelectedDate - 30
RETURN
SUMX(
FILTER(
Orders, -- Replace 'Orders' with the name of your table
Orders[DATE_REQUIRED] >= StartDate &&
Orders[DATE_REQUIRED] <= SelectedDate
),
Orders[QTY_ORDERED] - Orders[QTY_DELIVERED]
)

Add DateTable[Date] to the X-axis of your line chart.
Use the Open Orders (30 Days Prior) measure as the Y-axis.

💌 If this helped, a Kudos 👍 or Solution mark ✔️ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[FreemanZ]

hi @Voyageur83 ,

try like:

measure =
VAR _date = MAX(data[date_required])
VAR _result =
SUMX( FILTER(
data,
data[ date_required]>= _date- 30
&&data[ date_required] <= _date
),
QTY_ORDERED - QTY_DELIVERED
)
RETURN _result

[Sahir_Maharaj]

Hello @Voyageur83,

Can you please try this approach:

Open Orders 30 Days Prior = 
VAR CurrentDate = MAX('Orders'[DATE_REQUIRED])
VAR PriorDate = DATEADD(CurrentDate, -30, DAY)
RETURN
    CALCULATE(
        SUMX(
            FILTER(
                'Orders',
                'Orders'[DATE_REQUIRED] <= CurrentDate &&
                'Orders'[DATE_REQUIRED] > PriorDate &&
                'Orders'[QTY_ORDERED] > 'Orders'[QTY_DELIVERED]
            ),
            'Orders'[QTY_ORDERED] - 'Orders'[QTY_DELIVERED]
        )
    )