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 ...
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

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

Hi dennisdepoorter,

 

It's quite strange. I saw a dot rather a comma after "30" when I opened the report. I changed it into a comma. It ran into another issue, which can be solved easily by adding a Date table or using a formula like below.

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

Relative_date_filter_true_DAX2

 

Please don't share sensitive data here.

 

Best Regards,
Dale

  • dennisdepoorter's avatar
    dennisdepoorter
    Helper I
    7 years ago

    v-jiascu-msftthanks for the help, but I still have te same error... So I don't know what's going wrong :(

     

     
     
  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    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

  • dennisdepoorter's avatar
    dennisdepoorter
    Helper I
    7 years ago

    Hi Dale,

     

     

    THANKS THANKS THANKS!!! it was all in the ")" :-)

    Altough the formula works, it doesn't show what I want, but I will now dig into it myself...

     

    THANKS THANKS THANKS!!!!

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

    It's my pleasure. I'm so glad you can move on now.

     

    Best Regards,
    Dale