Evaluate each week ending date and find the earliest "open" order date for each week ending date

TwinJohnson — Fri, 11 Aug 2023 22:19:12 GMT

I have a date table that contains all dates and week ending. The raw data contains all order information. I'm able to identify the earliest open order date using the formula below. The formula below should be looking at each week ending date from my date table and identify if the order was open (date of initial review is blank) and if it's blank, find the oldest order date from the entire raw order data table (not just the week ending). I'm unsure how to write the DAX formula to show the results. Thank you

Table names: Date Table [week ending], Log [Order Date] , Log [Date of Initial Review]

FIRST DATE= CALCULATE(

min('Log'[Order Date]),

FILTER(

ALL(' Log'),

ISBLANK('Log'[Date of Initial Review])

)

The logic can hopefully be understood with the example below from the raw data table, date table is not shown.

Re: Evaluate each week ending date and find the earliest "open" order date for each week ending date

Mahesh0016 — Sat, 12 Aug 2023 11:35:43 GMT

TwinJohnson Please Can you share your EndOutPut in table?

Re: Evaluate each week ending date and find the earliest "open" order date for each week ending date

TwinJohnson — Tue, 15 Aug 2023 16:11:54 GMT

Hi Mahesh0016,

thank you for reaching out. The end output is the "Data Table Results - Earliest Order Date". The logic behind it is also visually displayed in which On week ending 01/05-03/23 it looks to see if the order was open. The order is open if the closed date was still open in each week ending expression. I have a dummy pbix file and xls file to share but unsure how to attach it here so I've pasted the table below.

This formula works to find the most current earliest open order date but doesn't find the earliest order date each week ending from the date table.

Table names: Date Table [week ending], Log [Order Date] , Log [Date of Initial Review]

FIRST DATE= CALCULATE(min('Log'[Order Date]),FILTER(ALL(' Log'), ISBLANK('Log'[Closed Date]))

Thank you again! Tawnee

RAW Table

Date Table

DATA Table Results

On week ending:

01/05 order was open?

On 01/12 order was open?

On 01/19 order was open?

On 01/26 order was open?

On 02/02 order was open?

On 02/09 order was open?

On 02/16 order was open?

On 02/23 order was open?

On 03/02 order was open?

On 03/09 order was open?

On 03/16 order was open?

On 03/23 order was open?

Order #

Order Date

Closed Date

Date Table Week Ending Date

Results = Earliest Order Date

72787152

01/02/19

03/06/19

1/5/2019

1/2/2019

72774457

01/02/19

03/21/19

1/5/2019

1/2/2019

72774465

01/02/19

03/21/19

1/5/2019

1/2/2019

72776825

01/02/19

03/21/19

1/5/2019

1/2/2019

72776833

01/02/19

03/21/19

1/5/2019

1/2/2019

72793176

01/06/19

03/10/19

1/12/2019

1/2/2019

72793192

01/06/19

03/21/19

1/12/2019

1/2/2019

72793168

01/07/19

03/21/19

1/12/2019

1/2/2019

72796963

01/08/19

03/08/19

1/12/2019

1/2/2019

72805491

01/14/19

03/12/19

1/19/2019

1/2/2019

72800708

01/14/19

03/13/19

1/19/2019

1/2/2019

72804544

01/14/19

03/13/19

1/19/2019

1/2/2019

72805269

01/14/19

03/13/19

1/19/2019

1/2/2019

72802555

01/15/19

03/13/19

1/19/2019

1/2/2019

72809055

01/15/19

03/13/19

1/19/2019

1/2/2019

72808610

01/16/19

03/13/19

1/19/2019

1/2/2019

72809014

01/16/19

03/14/19

1/19/2019

1/2/2019

72810343

01/16/19

03/14/19

1/19/2019

1/2/2019

72816894

01/20/19

03/18/19

1/26/2019

1/2/2019

72819633

01/20/19

03/18/19

1/26/2019

1/2/2019

72819625

01/21/19

03/20/19

1/26/2019

1/2/2019

72819468

01/23/19

03/22/19

1/26/2019

1/2/2019

72819484

01/23/19

03/22/19

1/26/2019

1/2/2019

72822231

01/23/19

03/22/19

1/26/2019

1/2/2019

72822256

01/23/19

03/22/19

1/26/2019

1/2/2019

72827594

01/24/19

03/28/19

1/26/2019

1/24/2023

72822082

01/25/19

03/22/19

1/26/2019

1/24/2023

Re: Evaluate each week ending date and find the earliest "open" order date for each week ending date

TwinJohnson — Thu, 17 Aug 2023 23:48:58 GMT

it's looking at each week ending what the earliest date is rather than looking at the entire data table to see what's open on that week ending (snapshot).

topic Re: Evaluate each week ending date and find the earliest "open" order date for each week ending date in DAX Commands and Tips

Evaluate each week ending date and find the earliest "open" order date for each week ending date

Re: Evaluate each week ending date and find the earliest "open" order date for each week ending date

Re: Evaluate each week ending date and find the earliest "open" order date for each week ending date

Re: Evaluate each week ending date and find the earliest "open" order date for each week ending date