Is order date in range

[Anonymous]

Hi Guys

I have a table with dim customer delivery schedule in format like this 

country	cst	seq	ref	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
ES	2062	004D1	ES2062004D1	1	1	1	1	1	0	0
ES	2062	005D1	ES2062005D1	1	1	0	1	0	0	0
ES	2062	006D1	ES2062006D1	1	1	1	1	1	0	0
ES	2062	007D1	ES2062007D1	0	1	1	1	1	0	0
ES	2062	008D1	ES2062008D1	1	1	1	0	1	0	0
ES	2062	009D1	ES2062009D1	1	1	1	1	1	0	0
ES	2062	011D1	ES2062011D1	1	1	1	1	1	0	0
ES	2064	001D1	ES2064001D1	1	0	1	1	1	0	0
ES	3482	000D1	ES3482000D1	1	1	1	1	1	0	0
ES	3482	003D1	ES3482003D1	1	1	1	1	1	0	0
ES	3492	000D1	ES3492000D1	1	1	0	1	1	0	0
ES	3515	001D1	ES3515001D1	0	1	1	1	1	0	0
ES	3523	000D1	ES3523000D1	0	1	0	1	1	1	0
ES	3543	000D1	ES3543000D1	1	1	0	1	1	0	0
ES	3578	001D1	ES3578001D1	0	1	1	0	1	0	0
ES	4481	001D1	ES4481001D1	1	1	1	1	0	0	0
ES	9426	000D1	ES9426000D1	1	1	0	1	1	0	0
ES	3256	000Z0	ES3256000Z0	0	1	1	1	0	0	0

where it basically says the cust with reference ES2062004D1 (Country&cst&seq) only accepts delivery on mo-fr, 

cust with reference ES2062005D1(Country&cst&seq) only accepts delivery on mo, tue and thu. etc. 

Then I have fact table where i have orders with delivery date

Order #	Country	Cst	Seq	ref	del date
12365	ES	2062	004D1	ES2062004D1	19-10-20
12366	ES	2062	004D1	ES2062004D1	20-10-20
12367	ES	2062	004D1	ES2062004D1	21-10-20
12368	ES	2062	004D1	ES2062004D1	22-10-20
12369	ES	2062	004D1	ES2062004D1	23-10-20
12370	ES	2062	004D1	ES2062004D1	24-10-20
12371	ES	2062	004D1	ES2062004D1	25-10-20
12372	ES	2062	004D1	ES2062004D1	26-10-20
12373	ES	2062	004D1	ES2062004D1	27-10-20
12374	ES	2062	004D1	ES2062004D1	28-10-20
12375	ES	2062	004D1	ES2062004D1	29-10-20
12376	ES	2062	004D1	ES2062004D1	30-10-20
12377	ES	2062	004D1	ES2062004D1	31-10-20
12378	ES	2062	004D1	ES2062004D1	01-11-20
12379	ES	2062	004D1	ES2062004D1	02-11-20
12380	ES	2062	004D1	ES2062004D1	03-11-20
12381	ES	2062	004D1	ES2062004D1	04-11-20
12382	ES	2062	004D1	ES2062004D1	05-11-20

what i want to achieve is I want to know if the del date is a day when the customer accepts delivery.

What is the best solution

I am thinking to create a calculated column that would calculate something like 

if dim customer delivery schedule ref  = fact ref and the delivery date is on days 1-5 then 1 else 0

- how should i approach this should I add this table to the model and create relationship 

- should i not connect it to the model and use is as lookup table - this can be eventually done also in the query editor (i would assume)

- or somehow create calculated column using dax 

I would be very thankful if someone have experience with similar scenario and can recommend a solution or at least suggest direction

thank you very much 

miba

[v-kelly-msft]

Hi @Anonymous ,

First go to power query>select Columns Mon-Sun >unpivot the columns;

Then back to model view>create a relationship between the fact tabel and the unpivot table;

Then create a calculated column as below:

Column = 
var _search=LOOKUPVALUE('Unpivot table'[Value],'Unpivot table'[ref],'Table (2)'[ref],0)
var _search2=LOOKUPVALUE('Unpivot table'[Value],'Unpivot table'[Attribute],'Table (2)'[Weekday],0)
Return
IF(_search=1&&_search2=1,TRUE(),FALSE())

Or a measure as below:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

I would like to help.  The second table, when pasted in MS Excel, does not appear in a proper format.  The first one appears just fine.  Please paste both tables in an MS Excel workbook and share the download link of that workbook.

[camargos88 · Accepted solution]

Hi @Anonymous ,

I would transform (unpivoting the day columns) the dates on dim customer delivery schedule to rows, having only two columns (day and delivery)..

so you can calculate if your customer has 1 on those dates comparing with another table....

See the attached solution using Power Query.

Here I've created a column on delivery table to check if has the date or not.

[richbenmintz]

Hi @Anonymous,

You can try the following, 

-> create a new column in your customer dimension like:

Accepts Delivery = 
if([Monday] = 1, "2|", "|" ) &
if([Tuesday] = 1, "3|", "|" ) &
if([Wednesday] = 1, "4|", "|" ) &
if([Thursday] = 1, "5|", "|" ) &
if([Friday] = 1, "6|", "|" ) &
if([Saturday] = 1, "7|", "|" )&
if([Sunday] = 1, "1|", "|" ) 

-> create a day of week column in your fact table (type == text)

Day of Week = WEEKDAY([del date])

-> create a calc in your fact table to identify if the day of week exists in the Accepts Delivery String

Good Date = if(FIND([Day of Week], RELATED('dim customer delivery schedule'[Accepts Delivery]), 1, 0)>0, TRUE(), FALSE())

Hope this Helps,
Richard
Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!