topic Re: Get last non 0 order qty for a week in DAX Commands and Tips

Get last non 0 order qty for a week

NSC7 — Tue, 28 Mar 2023 21:48:57 GMT

Hi All,

I have a table with below data. I am trying to write DAX to get value for Qty PAB column.

Qty PAB column should show Order_Qty from the Max due_date of the week where we have non 0 value and if any week value is 0 for all Due dates Order_Qty should be picked from MAX(Due_Date) where Order_Qty > 0 and Due_Date <= Last Day of Week.

Ex-1

Ex -2

I tried below Dax but it is not working.

Qty PAB =

VAR _maxdate = CALCULATE(MAX(Query1[Due_date]),
FILTER(Query1,Query1[Order_Qty]>0 && Query1[Order_Type]= "PAB" && Query1[Due_date] <= SELECTEDVALUE(Query1[Last Day of Week] )))
RETURN CALCULATE(MAX(Query1[Order_Qty]),Query1[Due_date]=_maxdate)

Thanks

Re: Get last non 0 order qty for a week

Greg_Deckler — Wed, 29 Mar 2023 01:04:41 GMT

NSC7 Try:

Qty PAB Measure = VAR __MaxNZDate = MAXX(FILTER(ALL('Query1'),[Order_Qty] > 0), [Due Date]) VAR __Result = MAXX(FILTER(ALL('Query1'), [Order_Qty] > 0 && [Due Date] = __MaxNZDate),[Order_Qty]) RETURN __Result

Re: Get last non 0 order qty for a week

NSC7 — Wed, 29 Mar 2023 12:56:40 GMT

Greg_Deckler , Thanks for replying but It didn't work. I think i didn't mention the requirement clearly.

Data I am showing is just sample data, there are multiple months and Weeks and multiple values for Display Column. Please check below screenshot. Please let me know if you have more questions.

Re: Get last non 0 order qty for a week

Greg_Deckler — Wed, 29 Mar 2023 15:32:26 GMT

NSC7 Can you post the sample data as text?

Re: Get last non 0 order qty for a week

NSC7 — Wed, 29 Mar 2023 16:59:06 GMT

Greg_Deckler , Please find below

Item	Month Of	Week Of	First Day of Week	Due_date	Last Day of Week	Display	Order_Qty
1	23-Mar	Previous Week	03/20/23	03/20/23	03/26/23	PAB	0
1	23-Mar	Previous Week	03/20/23	03/22/23	03/26/23	PAB	100
1	23-Mar	Previous Week	03/20/23	03/23/23	03/26/23	PAB	105
1	23-Mar	Previous Week	03/20/23	03/24/23	03/26/23	PAB	0
1	23-Mar	3/27/2023	03/27/23	03/27/23	04/02/23	PAB	0
1	23-Mar	3/27/2023	03/27/23	03/28/23	04/02/23	PAB	0
1	23-Mar	3/27/2023	03/27/23	03/29/23	04/02/23	PAB	0
1	23-Mar	3/27/2023	03/27/23	03/30/23	04/02/23	PAB	0
1	23-Mar	3/27/2023	03/27/23	03/31/23	04/02/23	PAB	0
1	23-Mar	3/27/2023	03/27/23	04/01/23	04/02/23	PAB	0
1	23-Mar	3/27/2023	03/27/23	04/02/23	04/02/23	PAB	0
1	23-Mar	Previous Week	03/20/23	03/20/23	03/26/23	PAB$	0
1	23-Mar	Previous Week	03/20/23	03/22/23	03/26/23	PAB$	150
1	23-Mar	Previous Week	03/20/23	03/23/23	03/26/23	PAB$	200
1	23-Mar	Previous Week	03/20/23	03/24/23	03/26/23	PAB$	0
1	23-Mar	3/27/2023	03/27/23	03/27/23	04/02/23	PAB$	0
1	23-Mar	3/27/2023	03/27/23	03/28/23	04/02/23	PAB$	0
1	23-Mar	3/27/2023	03/27/23	03/29/23	04/02/23	PAB$	0
1	23-Mar	3/27/2023	03/27/23	03/30/23	04/02/23	PAB$	0
1	23-Mar	3/27/2023	03/27/23	03/31/23	04/02/23	PAB$	0
1	23-Mar	3/27/2023	03/27/23	04/01/23	04/02/23	PAB$	0
1	23-Mar	3/27/2023	03/27/23	04/02/23	04/02/23	PAB$	0
1	23-Mar	Previous Week	03/20/23	03/20/23	03/26/23	POH	250
1	23-Mar	Previous Week	03/20/23	03/22/23	03/26/23	POH	0
1	23-Mar	Previous Week	03/20/23	03/23/23	03/26/23	POH	0
1	23-Mar	Previous Week	03/20/23	03/24/23	03/26/23	POH	0
1	23-Mar	3/27/2023	03/27/23	03/27/23	04/02/23	POH	0
1	23-Mar	3/27/2023	03/27/23	03/28/23	04/02/23	POH	0
1	23-Mar	3/27/2023	03/27/23	03/29/23	04/02/23	POH	0
1	23-Mar	3/27/2023	03/27/23	03/30/23	04/02/23	POH	0
1	23-Mar	3/27/2023	03/27/23	03/31/23	04/02/23	POH	0
1	23-Mar	3/27/2023	03/27/23	04/01/23	04/02/23	POH	0
1	23-Mar	3/27/2023	03/27/23	04/02/23	04/02/23	POH	0

Expected output-

		23-Mar
Item	Display	Previous Week	3/27/2023
1	PAB	105	105
1	PAB$	200	200
1	POH	250	250

Thanks

Re: Get last non 0 order qty for a week

NSC7 — Thu, 30 Mar 2023 12:46:18 GMT

Greg_Deckler , Please let me know if you need more details.