Return date value from date table that is X number of FILTERED dates away

[aallman]

Hello! I need some help with a DAX formula please!

I need to return a Calculated Ship Date column. This is based on Order Date + X number of working days. My date table has each date indicated as a working day Y/N (actually it is 1 or 0). How can I use the date table, start with my Order Date and move up X rows where WorkingDay = 1 and return that date?

For example - I need to add a column to my data table called "Calculated Ship Date" and it should return 1/9/24.

My Data Table

OrderDate	Location	Working Days	Calculated Ship Date
1/4/24	USA	3	1/9/24

DateTable

Date	USA WorkingDay	Asia WorkingDay
1/4/24	1	1
1/5/24	1	1
1/6/24	0	1
1/7/24	0	0
1/8/24	1	0
1/9/24	1	1

[barritown · Accepted solution]

Hi @aallman,

You can try this one:

DAX code in plain text:

Calculated Shipping Date = 
VAR currentDate = [OrderDate]
VAR currentLoc = [Location]
VAR workDay = [Working Days]
RETURN SWITCH ( TRUE(),
    currentLoc = "USA",
    VAR _tbl = FILTER ( ref, [USA WorkingDay] = 1 && [Date] > currentDate )
    VAR _tbl2 = ADDCOLUMNS ( _tbl, "ID", RANKX ( _tbl, [Date], , ASC ) )
    RETURN MINX ( FILTER ( _tbl2, [ID] = workDay ), [Date] ),
    currentLoc = "Asia",
    VAR _tbl = FILTER ( ref, [Asia WorkingDay] = 1 && [Date] > currentDate )
    VAR _tbl2 = ADDCOLUMNS ( _tbl, "ID", RANKX ( _tbl, [Date], , ASC ) )
    RETURN MINX ( FILTER ( _tbl2, [ID] = workDay ), [Date] )
    )

Please also find the attached PBIX file.

If you can change your DateTable (Date - Location - Working Day Flag), the calculation column can be simplified.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian