Filter with date slicer and dynamic offset

[Anonymous]

Hello,

I am trying to solve a situation with date slicers and a custom offset. I will try to explain it.

I have a table with orders and returns like this:

id	order_date	return_date	units_bought	units_returned
3	2020-04-20	null	1	0
4	2020-04-25	null	1	0
1	2020-04-27	2020-05-06	1	1
2	2020-04-28	2020-05-06	4	3

(If the order has not been returned, it will have a null value in return_date column)

Then, I have a date slicer linked with order_date column. So, when I select a time period from 1 of April to 30 of April, I will get all the lines of the table.

However, I want to add a value called "offset" that will be related to the time the client has to return the order. For example, an offset of 10 days, will say that if the order has been bought on 1 of april, it can be returned up to 10 of april.

Then, what I want is to use that offset to filter values of the table:

1) If the order hasn't any return, we should check that the order_date + offset is less than selected period. This is because we want to exclude the orders that are still live, the ones that the client can still return it

2) If the order has been returned, we should check that order_date + offset is less than return_date 

I have tried to start with the first condition, ignoring for the moment, the second one. However, my approach didn't work.

Here is the measure that I did:

var max_selected_date = MAX(dates[id]) --the max day of date slicer

var var_offset = SELECTEDVALUE(OFFSET[OFFSET]) --the selected offset value

metric = CALCULATE(
 SUM(orders[units_bought]);
 FILTER(
     orders;
     DATEADD(orders[order_date];var_offset;DAY) <= max_selected_date
     )
)--the valid orders

return metric

Imagine that I select a date period from 1 to 30 of April, and an offset of 4 days. What I want is only the orders of the days 20 and 25 of april. The orders of 27 and 28 should not be considered because 27 + 4days = 1 of May, and 1 of May is greather than 30 of April. The order of 25 of may should be considered because 25 + 4 days = 29 of April, and 29 of April is included in the selected date period.

What I am doing wrong with my formula?

Best regards!

[stevedep]

What exactly is going wrong? Which output are you getting / not getting compared to expected?

[Anonymous]

The problem is that the orders are not filtered. It seems that the dateadd function is not working

[Anonymous]

Of course DATEADD does not work. If you take a good look at the documentation of this function, you'll see that it returns a table of dates (and this function should be used on a proper Date table, not on a column in a fact table - but the docs won't tell you this).

Secondly, this line

DATEADD(orders[order_date];var_offset;DAY) <= max_selected_date

is not syntactically correct because you cannot compare a table to a scalar. Hence, it does not filter anything.

Thirdly, you should create good models, not just ANY models. If your current model has just one big fact table without any dimensions (and I can see you don't have any Date dimension)... then I'd suggest you change this immediately if you don't want to have issues down the line.

Here's something to learn about time-intelligence in DAX:

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

and here's a start to how to build correct models:

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.youtube.com/watch?v=_quTwyvDfG0

Best
D

[stevedep · Accepted solution]

Like this?

sales_sum_slicer = 
VAR MaxDate = MAX ('Date'[Date] ) 
VAR MinDate = MIN('Date'[Date])
VAR DaysBeforeDatea= MaxDate - 4
RETURN

    CALCULATE (
       CALCULATE(SUM('Table'[units_bought]));          --
      'Date'[Date]<= DaysBeforeDatea; 'Date'[Date] >= MinDate;  
        ALL ( 'Date' )               
    )

File is here.

Kind regards, Steve 

pls mark as solved when your query has been answered.