cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Accepted Solutions
Highlighted
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
Highlighted
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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors