Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
TwinJohnson
Frequent Visitor

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

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.

TwinJohnson_0-1691792299128.png

 

3 REPLIES 3
TwinJohnson
Frequent Visitor

TwinJohnson_0-1692316071296.png

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

 

 

Mahesh0016
Super User
Super User

@TwinJohnson Please Can you share your EndOutPut in table?

 

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 DateClosed Date Date Table Week Ending DateResults = Earliest Order Date              
7278715201/02/1903/06/19 1/5/20191/2/2019  YYYYYYYYYNNN
7277445701/02/1903/21/19 1/5/20191/2/2019  YYYYYYYYYYYN
7277446501/02/1903/21/19 1/5/20191/2/2019  YYYYYYYYYYYN
7277682501/02/1903/21/19 1/5/20191/2/2019  YYYYYYYYYYYN
7277683301/02/1903/21/19 1/5/20191/2/2019  YYYYYYYYYYYN
7279317601/06/1903/10/19 1/12/20191/2/2019  YYYYYYYYYYNN
7279319201/06/1903/21/19 1/12/20191/2/2019  YYYYYYYYYYYN
7279316801/07/1903/21/19 1/12/20191/2/2019  YYYYYYYYYYYN
7279696301/08/1903/08/19 1/12/20191/2/2019  YYYYYYYYYNNN
7280549101/14/1903/12/19 1/19/20191/2/2019  YYYYYYYYYYNN
7280070801/14/1903/13/19 1/19/20191/2/2019  YYYYYYYYYYNN
7280454401/14/1903/13/19 1/19/20191/2/2019  YYYYYYYYYYNN
7280526901/14/1903/13/19 1/19/20191/2/2019  YYYYYYYYYYNN
7280255501/15/1903/13/19 1/19/20191/2/2019  YYYYYYYYYYNN
7280905501/15/1903/13/19 1/19/20191/2/2019  YYYYYYYYYYNN
7280861001/16/1903/13/19 1/19/20191/2/2019  YYYYYYYYYYNN
7280901401/16/1903/14/19 1/19/20191/2/2019  YYYYYYYYYYNN
7281034301/16/1903/14/19 1/19/20191/2/2019  YYYYYYYYYYNN
7281689401/20/1903/18/19 1/26/20191/2/2019  YYYYYYYYYYYN
7281963301/20/1903/18/19 1/26/20191/2/2019  YYYYYYYYYYYN
7281962501/21/1903/20/19 1/26/20191/2/2019  YYYYYYYYYYYN
7281946801/23/1903/22/19 1/26/20191/2/2019  YYYYYYYYYYYN
7281948401/23/1903/22/19 1/26/20191/2/2019  YYYYYYYYYYYN
7282223101/23/1903/22/19 1/26/20191/2/2019  YYYYYYYYYYYN
7282225601/23/1903/22/19 1/26/20191/2/2019  YYYYYYYYYYYN
7282759401/24/1903/28/19 1/26/20191/24/2023  YYYYYYYYYYYY
7282208201/25/1903/22/19 1/26/20191/24/2023  YYYYYYYYYYYN

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.