Get last non 0 order qty for a week

[NSC7]

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

[Greg_Deckler]

@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

[NSC7]

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

[Greg_Deckler]

@NSC7 Can you post the sample data as text?

[NSC7]

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

[NSC7]

@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