Forum Discussion
Filter with date slicer and dynamic offset
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 uni...
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.
stevedep
Memorable Member
Memorable MemberWhat exactly is going wrong? Which output are you getting / not getting compared to expected?
The problem is that the orders are not filtered. It seems that the dateadd function is not working
- 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
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.