Firstdate or Min not working when trying to ignore filters

[Asantos2020]

Hello there,

The DAX measure below is not working:

FirstPurchase = CALCULATE(FIRSTDATE(SalesFactTable[Date]);ALL('Calendário'[Date]))

I need to use it on a table visual, as follows:

Customer   Last Purchase   Purchase Cycle    First Purchase

A                  09/29/2019               4                09/25/2019

B                  09/15/2019              15               09/01/2019

The data above is from a SalesFactTable and I have a Calendar Table on a slicer and which filters the SalesFactTable.

I've tried so many possibilities and some give me 12/31/1899.

Thanks in advance!

[MFelix]

Hi @Asantos2020 ,

Measure are based on context and using the ALL function on your measure returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

Looking at your calculation and I'm assuming that your calendário table is connected to the sales what is happening is that you are picking up the minimum date of your calendar since you are ignoring all the filters (changing the context).

Try the following code instead:

FirstPurchase =
CALCULATE (
    FIRSTDATE ( SalesFactTable[Date] );
    ALL ( 'Calendário'[Date] );
    FILTER (
        ALL ( SalesFactTable[Customer]; SalesFactTable[Purchase Cycle] );
        SalesFactTable[Customer] = MAX ( SalesFactTable[Customer] )
            && SalesFactTable[Purchase Cycle] = MAX ( SalesFactTable[Purchase Cycle] )
    )
)

If this doesn't work can you share a sample of your data and expected result?

Regards,

MFelix

[Asantos2020 · Accepted solution]

Hello @MFelix ,

Here is how I've fixed it:

Primeira Compra = CALCULATE(FIRSTDATE('Calendário'[Date]);
                            ALL('Calendário'[Date]);
                            FILTER(
                                ALL(SalesTable);
                                SalesTable[ClienteId] = MAX(SalesTable[ClienteId])
                            ))

Thanks a lot for your help!

[MFelix]

Hi @Asantos2020 ,

On your example wasn't clear that one of the columns was a measure, but a measure is based on context and this can be given by other measures on your case that is what happened.

Regards,

Mfelix

[Asantos2020]

Hello, @MFelix .

Sorry I forgot to mention that the Purchase Cycle is a measure that has FirstPurchase as a calculation factor. Therefore, I'd assume that I should use another implicit column in the DAX formula, like CustomerID...?

I did so, but the column presented the correct first purchase for a couple of customers in the list only. The majority resulted in 31/12/1899.

At the end, we need to have both FIRST PURCHASE and LASTPURCHASE ignore date filter, so that I can have a Purchase Cycle for customer x, y and z to let Sales Team know as to those customers' purchasing profile.

Appreciate your attention on this one!

Regards,

Antonio Santos