Find ID's not present in a date represented by a yyyyweek_number

Question

I've 2 data sets, one which represts a list of all of the customers and other with their order dates The order date are in a yyyyweek_number format, so for instance as today (2020-09-29) is week 40, the order date would be represented as 202040

I want to get a list of dealers who haven't placed orders in 4 day ranges viz. 30 days or less 60 days or less 90 days or less and 90+ days

To illustrate lets say the customer dataset is as under:

+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+----+

and the Order table is as under:

+----+-----------------+
| ID | Order_YYYY_WEEK |
+----+-----------------+
|  1 |          202001 |
|  2 |          202003 |
|  3 |          202004 |
|  5 |          202006 |
|  2 |          202008 |
|  3 |          202010 |
|  6 |          202012 |
|  8 |          202009 |
|  1 |          202005 |
| 10 |          202015 |
| 11 |          202018 |
| 13 |          202038 |
| 15 |          202039 |
| 12 |          202040 |
+----+-----------------+

The slicer format that I've looks like this

Now say for instance the 30 days or less button is selected, the resulting table should represent a table as under, with all the ID's from the Customer table that aren't present in the ORDER table where ORDER_YYYY_WEEK is 30 days from todays week

+----+
| ID |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 14 |
+----+

Aloys Aloys · Accepted Answer · 2020-09-29T10:19:16

Steps:

Create relationship between Customer id's in Customer table and Order table (if not already there)
Create a Date table
Convert Weeks to dates in a new calculated column in the Order table
Create relationship between Customer id's in Customer table and Order table
Create relationship between Dates in Date table and Order table
Create calculated column in Date Table with Day ranges ("30 days or less" etc)
Create measure to identify if an order was placed
Add slicer with date range from Date table and table visual with Customer id.
Add measure to table visual on filter pane and set to "No"

Some of these steps have additional detail below.

2. Create a Date table

We can do this is PowerQuery or in DAX. Here's the DAX version:

Calendar = 
VAR 
    Days = CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) )
RETURN 

ADDCOLUMNS (
    Days,
    "Year Week", YEAR ( [Date] ) & WEEKNUM([Date])
)

Now mark this table as a date table in the "Table Tools" ribbon with the button "Mark as date table"

3. Convert Weeks to dates

For this to work, I have had to create a calculated column in the Order table with the first day of the year first. This can probably be improved upon.

StartYear = DATE(Left(Orders[Year week], 4), 01, 01)

Next the calculated column that we need in the Order table, that identifies the first day of the week. The Variable "DayNoInYear" takes the week number times 7 and substracting 7 to arrive at the first day of the week, returning the nth day of the year. This is then converted to a date with the variable "DateWeek":

Date = 
VAR DayNoInYear = RIGHT(Orders[Year week], 2) * 7 - 7
VAR DateWeek = DATEADD(Orders[StartYear].[Date], DayNoInYear, DAY)
RETURN
DateWeek

6. Create calculated column in Date Table with Day ranges

Day ranges = 
VAR Today = TODAY() 
VAR CheckDate = 'Calendar'[Date] RETURN
SWITCH(TRUE(),
    CheckDate - Today <= -90, "90+ days",
    CheckDate - Today <= -60 && CheckDate - Today > -90 , "90 days or less",
    CheckDate - Today <= -30 && CheckDate - Today > -60 , "60 days or less",
    CheckDate - Today <= 0 && CheckDate - Today > -30 , "30 days or less",
    "In the future"
)

7. Create measure to identify if an order was placed

Yes - No order = 
VAR Yes_No =
IF(
    ISBLANK(FIRSTNONBLANK(Orders[Customer id], Orders[Customer id])),
    "No", 
    "Yes"
)
VAR ThirtyDays = SELECTEDVALUE('Calendar'[Day ranges]) = "30 days or less"
VAR SixtyDays = SELECTEDVALUE('Calendar'[Day ranges]) = "30 days or less" || SELECTEDVALUE('Calendar'[Day ranges]) = "60 days or less"
VAR NinetyDays = SELECTEDVALUE('Calendar'[Day ranges]) = "30 days or less" || SELECTEDVALUE('Calendar'[Day ranges]) = "60 days or less" || SELECTEDVALUE('Calendar'[Day ranges]) = "90 days or less"
RETURN

SWITCH(TRUE(),
    AND(ThirtyDays = TRUE(), Yes_No = "No"), "No",
    AND(SixtyDays = TRUE(), Yes_No = "No"), "No",
    AND(NinetyDays = TRUE(), Yes_No = "No"), "No",
    Yes_No = "No",
    "Yes"
)

Steps 8 and 9

Create slicer with the newly created "Day range" column in the Date table and create a table visual with the "Yes - No order" measure as visual-level filter set at "No" as in screenshot attached below

Find ID's not present in a date represented by a yyyyweek_number

1 Answers