Relative date filter true DAX

[dennisdepoorter]

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

[v-jiascu-msft]

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]

@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]

Hi @dennisdepoorter,

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

Best Regards,
Dale

[dennisdepoorter]

@v-jiascu-msft  I did rebuild it (3 times ), but error keep showing up:

hmmmm 😞

thanks anyway... I can rebuild the database with less and changed data... but thats some hours work

[dennisdepoorter]

@v-jiascu-msft

ActivatedDate is a column with dates on when the order has been activated.

Thanks for the feedback all-ready, but don't work for columns that way (I think)

[v-jiascu-msft]

Hi @dennisdepoorter,

Can you share the file? It works as both a calculated column and a measure in my side.

Please mask the sensitive parts.

Best Regards,
Dale

[dennisdepoorter]

@v-jiascu-msft

The file is connected to our Salesforce enviroment... all data is confidential... is there another way?

Maybe this printscreen of the DTB can help you out?

 

 

[v-jiascu-msft]

Hi @dennisdepoorter,

I created a similar model. But no issues occurred. The most important thing is it shouldn't return an error message about the MIN function. I'm very sure. I would suggest you delete it and rebuild it again.

Best Regards,
Dale

[dennisdepoorter]

@v-jiascu-msft

I made a test enviroment... and have the same error.

Would be nice if you can give it a view!

https://www.dropbox.com/s/vupkg4chx07v5hf/test.pbix?dl=0

 Many many thanks!!!

[v-jiascu-msft]

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] )
    )
)

Please don't share sensitive data here.

Best Regards,
Dale

[dennisdepoorter]

@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 · Accepted solution]

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] )
    )
)

Best Regards,
Dale

[dennisdepoorter]

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]

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

Best Regards,
Dale

[v-jiascu-msft]

Hi @dennisdepoorter,

I created a similar model. But no issues occurred. The most important thing is it shouldn't return an error message about the MIN function. I'm very sure. I would suggest you delete it and rebuild it again.

Best Regards,
Dale