Working Day Calculations - DAX function help to work around scalar value

[curiouspbix0]

Hello Geeks,

Hitting roadblocks on dax here on trying to compute work day computations, see screen print below. Any help/ideas is appreciated.

Requirement - Two tables, Table 1- Transactional Data Table 2 - Working Day List

Expected Outcome - Combine these two tables to compute measure - Products Received for a given order date and Order Received Date Business Day combo.

Sample 1 - For Order Date 10 Jan, Products received on Second Business Day that is 13 Jan should count for Products Received on 13 Jan (4) + Products Received on prior Holidays (20 (14 Jan( + 20 (15 Jan) ) = 44

Table 1				Table 2
Order date	Order Received Date	Products Received		Business Day
10-Jan	10-Jan	1		10-Jan
10-Jan	11-Jan	20		13-Jan
10-Jan	12-Jan	20		15-Jan
10-Jan	13-Jan	4		16-Jan
10-Jan	14-Jan	3		17-Jan
13-Jan	13-Jan	9
13-Jan	14-Jan	7
13-Jan	15-Jan	9
15-Jan	15-Jan	8
15-Jan	16-Jan	9
15-Jan	17-Jan	1
Expected Outcome
Order date	Products Received Same Day	Products Received Second Business Day (Including holiday shipments)	Products Received Third Day (Including holiday shipments)
10-Jan	1	44	3
13-Jan	9	16	0
15-Jan	8	9	1

[v-yuta-msft · Accepted solution]

@curiouspbix0 ,

You can add an index column in table2, click query editor-> Transform-> Add Index Column, then create three measures as pattern below and drag Date column and the three measures to a table chart:

Products Received Second Business Day(Including holiday shipments) = 
VAR Current_Date = MAX(Table1[Order date])
VAR Current_Index = CALCULATE(MAX(Table2[Index]), FILTER(Table2, Table2[Business Day] = Current_Date))
VAR Second_Date = CALCULATE(MAX(Table2[Business Day]), FILTER(Table2, Table2[Index] = Current_Index + 1))
RETURN
CALCULATE(SUM(Table1[Products Received]), FILTER(Table1, Table1[Order Received Date] > Current_Date && Table1[Order Received Date] <= Second_Date))

Products Received Same Day = 
VAR Current_Order_Date = MAX(Table1[Order date])
VAR Current_Received_Date = CALCULATE(MAX(Table1[Order Received Date]), FILTER(Table1, Table1[Order Received Date] = Current_Order_Date))
VAR Current_Products = CALCULATE(MAX(Table1[Products Received]), FILTER(Table1, Table1[Order Received Date] = Current_Received_Date))
RETURN
IF(Current_Order_Date = Current_Received_Date, Current_Products)

Products Received Third Day (Including holiday shipments) = 
VAR Current_Date = MAX(Table1[Order date])
VAR Current_Index = CALCULATE(MAX(Table2[Index]), FILTER(Table2, Table2[Business Day] = Current_Date))
VAR Second_Date = CALCULATE(MAX(Table2[Business Day]), FILTER(Table2, Table2[Index] = Current_Index + 1))
VAR Current_Product = CALCULATE(MAX(Table1[Products Received]), FILTER(Table1, Table1[Order Received Date] = Current_Date))
VAR Second_Product = CALCULATE(MAX(Table1[Products Received]), FILTER(Table1, Table1[Order Received Date] = Second_Date))
RETURN
Second_Product - Current_Product

  

You may also refer to appendix.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[curiouspbix0]

Thanks Jimmy.

That is an awesome way of using a dax function, very impressive.

As an DAX novicer, am trying to still wrap my brains around the formula you have written using variables.

Products Received Second Business Day(Including holiday shipments) = 
VAR Current_Date = MAX(Table1[Order date])
VAR Current_Index = CALCULATE(MAX(Table2[Index]), FILTER(Table2, Table2[Business Day] = Current_Date))
VAR Second_Date = CALCULATE(MAX(Table2[Business Day]), FILTER(Table2, Table2[Index] = Current_Index + 1))
RETURN
CALCULATE(SUM(Table1[Products Received]), FILTER(Table1, Table1[Order Received Date] > Current_Date && Table1[Order Received Date] <= Second_Date))

If I recreate each variable as a new column the end result does not match up,  could you educate me on what basics am I missing here, as am trying to use this as a foundation to the larger requriement.

[v-yuta-msft]

@curiouspbix0 ,

You can't create calculate columns for each parameters because this may achieve the Maximun Date in one column. However, MAX(Table[Date]) in measure only means the current row context, not the Maximun value. I would suggest you to refer to: https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.