Forum Discussion

dennisdepoorter's avatar
dennisdepoorter
Helper I
7 years ago
Solved

Relative date filter true DAX

Hi all,

 

I'll try to get an additional column in my 'order product' table based on a relative (rolling) date.

I tought i figgured it out, but it just don't work. Can someone help me out with the code?

 

Quantity L30D = CALCULATE(SUM('Order Product'[Quantity]), DATESBETWEEN('Order'[ActivatedDate],MIN('Order'[ActivatedDate]-30),MIN('Order'[ActivatedDate])))

 The error is: De functie MIN accepteert alleen een kolomverwijzing als argument. ==> The function MIN only accept a column reference as an argument.

 

What am I doing wrong?

Thanks!

Dennis

 

 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi dennisdepoorter,

     

    That's impossible. I have added some comments in the snapshot below. It's a COMMA in the formula while it's a DOT in the error message. 

    1. Please upgrade the Desktop to the latest version.

    2. Try this formula.

    Quantity L30D = 
CALCULATE (
    SUM ( 'Order Product'[Quantity] ),
    DATESBETWEEN (
        'Order'[ActivatedDate].[Date],
        (MIN ( 'Order'[ActivatedDate] ) - 30),
        MIN ( 'Order'[ActivatedDate] )
    )
)

    Relative_date_filter_true_DAX3

     

    Best Regards,
    Dale

15 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Dennis,

     

    If this formula can give you the desired result, you can amend it like below. It's a grammar error.

     

    Quantity L30D =
CALCULATE (
    SUM ( 'Order Product'[Quantity] ),
    DATESBETWEEN (
        'Order'[ActivatedDate],
        MIN ( 'Order'[ActivatedDate] ) - 30,
        MIN ( 'Order'[ActivatedDate] )
    )
)

     

    Best Regards,
    Dale

    • dennisdepoorter's avatar
      dennisdepoorter
      Helper I
      7 years ago

      v-jiascu-msftthe code you provide isn't correct..

       

      .

       

      The error messages is (translated from dutch): The Syntax for MIN isn't correct. DAX(CALCULATE ( SUM ( 'Order Product'[Quantity] ), DATESBETWEEN ( 'Order'[ActivatedDate], MIN ( 'Order'[ActivatedDate] ) - 30. MIN ( 'Order'[ActivatedDate] ) )))).

       

      The problem is in the ")" it should be:

       

      Quantity L30D = 
CALCULATE (
    SUM ( 'Order Product'[Quantity] ),
    DATESBETWEEN (
        'Order'[ActivatedDate],
        MIN ( 'Order'[ActivatedDate]  - 30),
        MIN ( 'Order'[ActivatedDate] )
    )
)

      But then the error: "The MIN function only accepts a column reference as an argument"

       
       
       
      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi dennisdepoorter,

         

        Is [ActiveatedDate] a column or a measure? I made a few adjustments. It worked in my test.

        Relative_date_filter_true_DAX

         

        Best Regards,
        Dale