number of customers who increase their purchase

[Ion_Ander]

Hi,

I´m triying to do a DAx formula to obtain the numer of clients how increase treir purchase compared to the previous year.

So i have 3 tables

Company(only have have the name of the companies)

Clients (with name of the company, code of the client and the purchases of the client......)

DimDate2(custom date Table with continuous dates, year number, mont number, quarter number, quarter name........)

I try to use a countrows with summarize:

Increase=
 COUNTROWS(
            FILTER (
            SUMMARIZE (
                CLIENTS,
                CLIENTS[COMPANY],
                CLIENTS[CODCLI],
                "ytd", TOTALYTD ( SUM ( CLIENTS[IMPNET] ), DimDate2[DateFull]),
                "LYTD", CALCULATE ( SUM ( CLIENTS[IMPNET] ), SAMEPERIODLASTYEAR ( DimDate2[DateFull] ) ),
                "INCREASE", IF(CALCULATE ( SUM ( CLIENTS[IMPNET] ), SAMEPERIODLASTYEAR ( DimDate2[DateFull] ) )>
                TOTALYTD ( SUM ( CLIENTS[IMPNET] ), DimDate2[DateFull]),1,0)
            ),
           
            [INCREASE]=1
            --[ytd] > [LYTD]
            )
        )

Problem: Not returnin expected values, as yo can see if i put the client, their purchases in present year and purchases sameperiodlastyear there is many clients how increase their purchase, but when i put the result of the formula in a matrix (rows-->company name, columns--> quarter of the year:

Is something wrong in the DAX????

[Anonymous · Accepted solution]

Do you want a piece of golden advice? Here it is: Please stop using SUMMARIZE with calculations under it. SUMMARIZE should only be used for grouping rows. Never for anything else. Instead, you should use the combination SUMMARIZE/ADDCOLUMNS.

There's a full article on www.sqlbi.com by The DAX Gurus that explains why you should not do what you have.

And here's the measure:

// Assumptions:
// 1. Clients dimension that stores only unique clients.
// 3. Dates dimension for standard date calculations.
// 4. Purchases fact table that joins to the above dimensions
//    via a normal many-to-1 relationship with one-way filtering.
// Please bear in mind that fact tables must always be hidden
// and slicing can be done only via dimensions.

[Total Purchase] = SUM( Purchases[Amount] )

[# Cust. w/ Increase] =
COUNTROWS(
    FILTER( 
        // Purchases[ClientID] joins to Clients[ClientID]
        // and ClientID is a unique client identifier.
        DISTINCT( Purchases[ClientID] ),
        var PurchaseYTD = 
            CALCULATE(
                [Total Purchase],
                // Dates[Date] is the date column
                // without gaps in the date table
                // marked as such in the model. If this
                // is not true, time-intel functions
                // will not work properly.
                DATESYTD( Dates[Date] )
            )
        var PurchaseYTDLY =
            CALCULATE(
                [Total Purchase],
                DATESYTD( 
                    SAMEPERIODLASTYEAR(
                        Dates[Date]
                    )
                )
            )
        var PurchaseHasIncreased =
            PurchaseYTDLY < PurchaseYTD
        return
            PurchaseHasIncreased
    )
)

[Ion_Ander]

Thanks for the reply and for the advice.

But not sure about how to implement the ROWCOUNT. If i put tthe PurchaseHasIncreased in atable with ClientId and works fine, but i do´nt know how put this into the ROWCOUNT.

I tried this:

COUNTROWS(FILTER(CLIENTS, PurchaseHasIncreased=TRUE))

But if i put into a Card it show BLANK, and if put into a table with the ClientID show me inccremental numbers

[Anonymous]

@Ion_Ander, I gave you a measure. I have no idea what you're trying to do. Put this measure into visuals and see it working. If you want to see what Power BI is all about and how it works, you can watch some intro vids on YT. For instance, https://youtu.be/m1eLTtZHGs4

[Ion_Ander]

@Anonymous,Sorry, probably i´m doing something wrong, but if i try to put this measure (changing names of tables for my owns) give me a error.

I try to modifiy a few things to work, for example, I'm assuming that [Total Purchase] and [# Cust. w/ Increase] are variables and deleted the strollers and put the var behind.
Bbut i have problems,because i thought that the RETURN must go outside a variable.