CODEWITHSUNDEEP
powerbidax
kmax01
kmax01

Reputation: 69

Remove filters from Date column in DAX

I want to get the customer age using the first order date with online order type. The measure is working fine. Problem occures when I filter the month or order type using the slicers. What I want is no matter which date is selected, the customer age always calculated using the first online order date. I tried using the removefilter function but it is not working. May be I am not using it correctly.

customer-age =
var _firstdate = 
        CALCULATE(
                 FIRSTDATE(Orders[ORDER_CREATED]), 
                 FILTER(
                  Orders,
                  Orders[ORDER_TYPE_NAME] = "ONLINE"))
var _lastdate = TODAY()   
return
DATEDIFF(_firstdate, _lastdate, DAY)

Thanks in advance

PBI and Excel sheet download

Upvotes: 0

Views: 2645

Answers (3)

kmax01
kmax01

Reputation: 69

Sorry for the late reply as I was offline.

Thank you so much for your help and support. But unfortunately, none of the solutions worked for me :( . The method I used to get the required value is:

step-1: create a measure to find the minimum value min-date = calculate( min(orders[order_created]), FILTER( orders, orders[order_type] = "Online") )

step-2: create another measure to find days. datediff ([min-date], today(), day())

step-3: disable interaction of the date slicer so it won't affect the visual. Format>Edit interaction.

Don't know why the above solutions didn't work but this worked perfectly for me. Thanks anyway. Pro tips and suggestions are always welcome.

Upvotes: 1

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

Based on your comments, I think this is what you are looking for when you are filtering by the month of ORDER[ISSUE_CREATED]. If you are using any other filter, like a date table or the ORDER_CREATED column, this will not work.

customer-age =
VAR _firstdate =
    CALCULATE (
        MIN ( Orders[ISSUE_CREATED] ),
        FILTER ( Orders, Orders[ORDER_TYPE_NAME] = "ONLINE" ),
        REMOVEFILTERS ( ORDER[ISSUE_CREATED] )
    )
VAR _lastdate =
    TODAY ()
RETURN
    DATEDIFF ( _firstdate, _lastdate, DAY )

EDIT: please note I switched FIRSTDATE to MIN because FIRSTDATE will fail if there are duplicates.

EDIT 2: based on the file example, I changed the removefilters to work on ISSUE_CREATED. This must be created as a calculated column rather than a measure.

Upvotes: 1

Ozan Sen
Ozan Sen

Reputation: 2615

With new PBIX and Excel file shared by you, I updated my answer:

First Your DAX Code should be like this:

customer-age = 
var ordertypepicked =SELECTEDVALUE(Orders[Order Type Name])
var _firstdate = 
        CALCULATE(
                 MIN(Orders[Order Created]), 
                 ALLEXCEPT(Orders,Orders[Customer Name]))
var _lastdate = TODAY()
RETURN
DATEDIFF(_firstdate, _lastdate, DAY)

Upvotes: 1

Related Questions