MAXX and MINX as filtering on measures in a direct query

[hannaegils]

Hi!

I'm trying to create a measure of 'Retained Customers' by counting customernumbers and filtering on min&max dates but I can't get it to work. Since I'm using a direct query, creating a new filter column is not an option.

This is among the formulas I tried to use:

# Retained Customers =

CALCULATE(

COUNT(f_Sales[CustomerSK]);

MAXX(f_Sales;[TransDt]<20161000); 

MINX (f_Sales;[TransDt]>20161000) 

)

Can someone help?

[v-jiascu-msft]

Hi @hannaegils,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft · Accepted solution]

Hi @hannaegils,

1. What is your data source? It's still possible to create a column in my test -- SQL Server DB.

2. In your formula, the second and the third parameters will return errors.

3. It will return nothing even if the formula is good. If we add a "=" to the formula, it will be very clear.

a) MAXX(f_Sales;[TransDt]<=20161000)    return 20161000

b) MINX (f_Sales;[TransDt]>=20161000)    return 20161000

a & b without "=" returns nothing.

4. What are the conditions exactly? Maybe you can try it like this:

# Retained Customers =
VAR maxValue =
    CALCULATE ( MAX ( f_Sales[TransDt] ); f_Sales[TransDt] < "Your Value 1" )
VAR minValue =
    CALCULATE ( MIN ( f_Sales[TransDt] ); f_Sales[TransDt] > "Your Value 2" )
RETURN
    CALCULATE (
        COUNT ( f_Sales[CustomerSK] );
        FILTER (
            ALL ( f_Sales );
            f_Sales[TransDt] > minValue
                && f_Sales[TransDt] < maxValue
        )
    )

Best Regards!

Dale

[hannaegils]

Worked as a charm! Thanks you so much 🙂

[Ashish_Mathur]

Hi,

What kind of a date is 20161000?  Share a dataset and show the expected result.

[hannaegils]

Hi,

The column "transdt" that contains the value 20161000 is formatted as a whole number in the dataset, although I have a connection to another table where I have the dates on different date formats.

/Hanna

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[hannaegils]

Since you don't have access to our databases, a share of the file won't do any good here - will it?