Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
  • stevedep's avatar
    stevedep
    6 years ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago
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's avatar
stevedep
Icon for Memorable Member rankMemorable Member
6 years ago

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.