DAX calculating sales to new clients

[tux117]

Hi,

I'm trying to create a measure for calculating a sum of first-time sales. This is what my schema looks like.

 

Currently I have this measure,

// Get the sum of sales quantity for the date equal to the first day of sales
[FirstSaleAmount] =
CALCULATE(SUM(Sales[SalesQty]; FILTER(Sales; MIN(Sales[Date]) = FIRSTNONBLANK(VALUES(Sales[Date]); 1)))

which works only when sales data is sliced by both client and product. Any idea how should I modify it to get a valid sum over all clients?

Thanks

[v-ljerr-msft]

Hi @tux117,

Could you try the formula below to see if it works in your scenario? 

[FirstSaleAmount] =
SUMX (
    Client;
    SUMX (
        Product;
        CALCULATE (
            SUM ( Sales[SalesQty] );
            FILTER ( Sales; Sales[Date] = FIRSTNONBLANK ( VALUES ( Sales[Date] ); 1 ) )
        )
    )
)

Regards

[tux117]

Hmmm, it seems my reply has disappeared, so I'll have to write it once more.

@v-ljerr-msft unfortunately it does not work, the values in the table are right, when sliced by product and client, but overall result makes no sense.

 

I managed to do it the other way by creating a separate table containing dates of first sales

FirstTransactions = 
SUMMARIZE (
	Sales;
	Sales[Client];
	Sales[Product];
	"FirstSaleDate"; MIN ( Sales[Date] )
)

and then using it to create a custom column in the main table which shows if the record corresponds to the first sale,

IsFirstSale = 
CONTAINS(
	FirstTransactions; 
	FirstTransactions[Client]; Sales[Client]; 
	FirstTransactions[Product]; Sales[Product]; 
	FirstTransactions[FirstSaleDate]; Sales[Date]
)

The it's possible to use this column to filter

FirstSaleAmount2 = 
CALCULATE(
	SUM(Sales[SalesQty]); 
	Sales[IsFirstSale] = TRUE()
)

It looks pretty ugly but still works. From whar I've read about SUMMARIZE it must be possible to incorporate it into a measure use it in a filter without creating a "physical" table, but I didn't manage to do that.

[v-ljerr-msft · Accepted solution]

Hi @tux117,

From whar I've read about SUMMARIZE it must be possible to incorporate it into a measure use it in a filter without creating a "physical" table, but I didn't manage to do that.

---

Yes, you don't need to create the physical summarize table in this scenario. Based on my test, you should be able to use the formula below to create the calculate column without the physical table. 

IsFirstSale =
CONTAINS (
    SUMMARIZE (
        Sales;
        Sales[Client];
        Sales[Product];
        "FirstSaleDate"; MIN ( Sales[Date] )
    );
    [Client]; Sales[Client];
    [Product]; Sales[Product];
    [FirstSaleDate]; Sales[Date]
)

Regards

[tux117]

@v-ljerr-msft cool, thank you! That does the job.

[tux117]

.