cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

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:


IDOrder_YYYY_WEEK
1202001
2202003
3202004
5202006
2202008
3202010
6202012
8202009
1202005
10202015
11202018
13202038
15202039
12202040

 

The slicer format that I've looks like this with 4 buttons:
1. 30 days or less
2. 60  days or less
3. 90 days or less
4. 90+ days

 

dollarvora_1-1601356217520.png

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
1 ACCEPTED SOLUTION
Super User III
Super User III

Hi @dollarvora ,

 

Try adding the custom filter measure:

Selection = 
VAR CurrentWeek =
    CONVERT ( YEAR ( TODAY () ) & WEEKNUM ( TODAY () ); INTEGER )
VAR Time_Period =
    SWITCH ( SELECTEDVALUE ( Date_Range[ID] ); 1; 4; 2; 8; 3; 12; 99999 )
VAR Temp_Table =
    FILTER (
        SUMMARIZE ( 'Order'; 'Order'[ID]; 'Order'[Order_YYYY_WEEK] );
        'Order'[Order_YYYY_WEEK] <= CurrentWeek
            && 'Order'[Order_YYYY_WEEK] >= CurrentWeek - Time_Period
    )
RETURN
    IF (
        SELECTEDVALUE ( Customer[ID] ) <> MAXX ( Temp_Table; 'Order'[ID] );
        1;
        BLANK ()
    )

 

Be aware that your data is very reduced so be aware that this is not prepared for year changes, and only looking at the weeknumber.

 

If you want to have it with year changes I would do the following:

  • Add a custom column with week start date:
StartDate = 
var _year = LEFT('Order'[Order_YYYY_WEEK];4)
var _week = RIGHT('Order'[Order_YYYY_WEEK];2)
return
DATE(_year ; 1 ; 1) + (_week - 1 ) * 7

Now redo your measure to:

 

Selection_Based_on_Date = 
var today_week = DATE(YEAR(TODAY()) ; 1 ; 1) + (WEEKNUM(TODAY()) - 1 ) * 7
VAR Time_Period =
    SWITCH ( SELECTEDVALUE ( Date_Range[ID] ); 1; 30; 2; 60; 3; 90; 99999 )
VAR Temp_Table =
    FILTER (
        SUMMARIZE ( 'Order'; 'Order'[ID]; 'Order'[StartDate] );
        'Order'[StartDate] <= today_week
            && 'Order'[StartDate] >= today_week - Time_Period
    )
RETURN
    IF (
        SELECTEDVALUE ( Customer[ID] ) <> MAXX ( Temp_Table; 'Order'[ID] );
        1;
        BLANK ()
    )

 

Check PBIX file with both options.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

1 REPLY 1
Super User III
Super User III

Hi @dollarvora ,

 

Try adding the custom filter measure:

Selection = 
VAR CurrentWeek =
    CONVERT ( YEAR ( TODAY () ) & WEEKNUM ( TODAY () ); INTEGER )
VAR Time_Period =
    SWITCH ( SELECTEDVALUE ( Date_Range[ID] ); 1; 4; 2; 8; 3; 12; 99999 )
VAR Temp_Table =
    FILTER (
        SUMMARIZE ( 'Order'; 'Order'[ID]; 'Order'[Order_YYYY_WEEK] );
        'Order'[Order_YYYY_WEEK] <= CurrentWeek
            && 'Order'[Order_YYYY_WEEK] >= CurrentWeek - Time_Period
    )
RETURN
    IF (
        SELECTEDVALUE ( Customer[ID] ) <> MAXX ( Temp_Table; 'Order'[ID] );
        1;
        BLANK ()
    )

 

Be aware that your data is very reduced so be aware that this is not prepared for year changes, and only looking at the weeknumber.

 

If you want to have it with year changes I would do the following:

  • Add a custom column with week start date:
StartDate = 
var _year = LEFT('Order'[Order_YYYY_WEEK];4)
var _week = RIGHT('Order'[Order_YYYY_WEEK];2)
return
DATE(_year ; 1 ; 1) + (_week - 1 ) * 7

Now redo your measure to:

 

Selection_Based_on_Date = 
var today_week = DATE(YEAR(TODAY()) ; 1 ; 1) + (WEEKNUM(TODAY()) - 1 ) * 7
VAR Time_Period =
    SWITCH ( SELECTEDVALUE ( Date_Range[ID] ); 1; 30; 2; 60; 3; 90; 99999 )
VAR Temp_Table =
    FILTER (
        SUMMARIZE ( 'Order'; 'Order'[ID]; 'Order'[StartDate] );
        'Order'[StartDate] <= today_week
            && 'Order'[StartDate] >= today_week - Time_Period
    )
RETURN
    IF (
        SELECTEDVALUE ( Customer[ID] ) <> MAXX ( Temp_Table; 'Order'[ID] );
        1;
        BLANK ()
    )

 

Check PBIX file with both options.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors